Using SQL Server 8.00.2039 to create a pivot-like table of one field from each of 159 tables of 850k records each where all tables even the final "Answer" table has a primary key - without going over 15 gigabytes and overloading the temporary
file is my goal.
Because the Transform-Pivot command is non existent in this version, a way to place one column each from 159 tables, that have ~850k records each, into a column in the "Answer" table where all tables concern have a Primary key.
The "Answer" table should have 850k records with 159 additional columns other than the key and the two columns that concatenated to become the key.
Since I'd like to have the "Answer" table with 53 of the columns having the data type decimal(9,2) and 106 of the columns having the data type decimal(7,0), I created it first with the Primary key, and the 2 fields that concatenate to become the Primary
key and placed all zeroes in the 159 columns. That took like 45 seconds. But insert into won't acknowledge the key and tries to create an additional 850k rows. Select into will create the table from scratch giving all of the columns (38,0)
in the data type. I would go with that, but it gets to over 20 gigs while its running, and overloads the temp database and so
View Complete Post