Null values in CTAS statement
555204Aug 3 2007 — edited Aug 3 2007Hi all,
I've been working on upgrading a series of tables containing "old world" IDs to "new world" for an upgrade. These tables are pretty big (up to 250 million rows) so I've been using CTAS statements to create copies of the old world tables with the new world calculations applied to the IDs , then dropping the original and renaming.
Life was good.
Now I've discovered that these new tables will also include some new columns, the problem being that due to some poor legacy code the columns must be in a specific order. It seems I can't create xero length columns in a CTAS so now what I'm doing is creating empty tables including these new columns, then doing an insert into UP_XXX (SELECT ... FROM XXX) and inserting null where the new columns are.
Although this will probably work, I'm finding it heinously slow compared to the straight CTAS, and time is something of an issue as this will evenutally have to be done during a system outage.
Anyone got any better suggestions?