Posted by Kromey at 5:41pm Apr 21 '10Admittedly I'm not an expert in T-SQL (all my DBA work has been mostly standard SQL on MySQL, with some PostgreSQL and a mite dash of MS SQL and Oracle), but I'm pretty sure that the only way to do this is to run separate queries on each table.
So starting in 2009, you would do
INSERT INTO dest_table (schoolid, schoolname, lastdate, ...) VALUES SELECT schoolid, schoolname, 2009, ... FROM schools2009;
Then each subsequent table would be done in reverse chronological order (i.e. 2008, then 2007, then 2006...) with this query:
INSERT INTO dest_table (schoolid, schoolname, lastdate, ...) VALUES SELECT schoolid, schoolname, 2008, ... FROM schools2008 WHERE schoolid NOT IN (SELECT schoolid FROM dest_table); [Of course changing 2008 for each table.]
In English, this is literally going to each table and pulling out the schools that haven't already been added to the composite table. (I will not vouch for the absolute correctness of these queries -- they are first attempts done without testing and without specific knowledge of any oddities in T-SQL, as well as they may be leveraging MySQL-specific extension to standard SQL that simply don't exist in T-SQL. These queries rather are intended to get you started in the right direction.)
T-SQL may have some awesome ability to programmatically run through each table and run a tweaked version of this query, but otherwise I would suggest writing a small script in the scripting language of your choice to do this (because doing this bugger by hand would be a royal pain in the arse, especially when you realize you've forgotten to change the year on a couple of tables and have to start all over!).
For example, doing this in PHP you could generate each query like so:
So starting in 2009, you would do
INSERT INTO dest_table (schoolid, schoolname, lastdate, ...) VALUES SELECT schoolid, schoolname, 2009, ... FROM schools2009;
Then each subsequent table would be done in reverse chronological order (i.e. 2008, then 2007, then 2006...) with this query:
INSERT INTO dest_table (schoolid, schoolname, lastdate, ...) VALUES SELECT schoolid, schoolname, 2008, ... FROM schools2008 WHERE schoolid NOT IN (SELECT schoolid FROM dest_table); [Of course changing 2008 for each table.]
In English, this is literally going to each table and pulling out the schools that haven't already been added to the composite table. (I will not vouch for the absolute correctness of these queries -- they are first attempts done without testing and without specific knowledge of any oddities in T-SQL, as well as they may be leveraging MySQL-specific extension to standard SQL that simply don't exist in T-SQL. These queries rather are intended to get you started in the right direction.)
T-SQL may have some awesome ability to programmatically run through each table and run a tweaked version of this query, but otherwise I would suggest writing a small script in the scripting language of your choice to do this (because doing this bugger by hand would be a royal pain in the arse, especially when you realize you've forgotten to change the year on a couple of tables and have to start all over!).
For example, doing this in PHP you could generate each query like so:
$years = array(2008, 2007, 2006, ..., 1986);This code snippet assumes that the first query is run outside of this loop.
foreach($years as $year)
{
$query = "INSERT INTO dest_table (schoolid, schoolname, lastdate, ...) VALUES SELECT schoolid, schoolname, {$year}, ... FROM schools{$year} WHERE schoolid NOT IN (SELECT schoolid FROM dest_table);"
//then run the query
}
Yeesh, tough one - Kromey