February 5, 2009 at 2:30 pm
Hi,
Let's say I have a table with 5 columns (1 unique ID and 4 attribute columns)
RECID int,
COL_A varchar(10),
COL_B varchar(10),
COL_C varchar(10),
COL_D varchar(10)
I need to split the table vertically into 4 tables as below
TABLE 1 - RECID, COL_A
TABLE 2 - RECID, COL_B
TABLE 3 - RECID, COL_C
TABLE 4 - RECID, COL_D
The simple SQL statement will be
select RECID, COL_A
into TABLE_1
from MyTable
and repeat 3 more times
But the problem is that there are over 25 attribute columns and total 1 billion records.
Is there any way I can have the same results without having 25 "select into" statements and 25 "table scans" ?
Is there something like read the input once and write into 25 different output tables?
Thanks a lot in advance.
February 5, 2009 at 3:01 pm
Nope. It's going to be one insert statement per table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2009 at 4:45 pm
One of our developers tried to write a batch job with a cursor to read the input table once and write parts of it out to three different tables. It was agonizingly slow. Changing it to three different inserts made it run in a tiny fraction of the time, even though it had to read through the input table three times. I'm afraid you are better off to just go ahead and do it for however many tables you need to create.
That said, here is a hairbrained scheme. Use the OUTPUT clause. Do an insert into a table that has recID,columnA,columnB, but use OUTPUT to send recID,columnB to another table. When it's done running, drop columnB from table that was the primary target of the INSERT.
Warning: I have NEVER attempted what I just proposed, and with a billion rows it might well blow up in your face. So, if you want to experiment, do so at your own risk... and, if you survive, be sure to post whether or not it worked. 😛
But seriously, just do the inserts for each table. Its the safe way to proceed.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply