RE: Is it possible to insert column between existing columns using the alter table wizard?

I had to do this once in the past.  It was DB2, but the concept is the same.

What I did was:

CREATE TABLE myschema.mycopy AS (
    SELECT  COL_A, COL_B, COL_C, 'dummy' as NEW_COL, COL_D, COL_E
    FROM oldschema.oldtable) WITH DATA ;

DROP TABLE oldschema.oldtable ;

CREATE TABLE oldschema.oldtable as (select * from myschema.mycopy) WITH DATA ;

Good luck.  ___BACKUP___ first.  Then back it up again, and then again - onto three different mediums.  (media?)

Dennis

From: [email protected] [mailto:[email protected]] On Behalf Of SCHROEDER, NATHAN E (AG/1000)
Sent: Thursday, May 24, 2012 1:19 PM
To: [email protected]
Subject: [toad] RE: Is it possible to insert column between existing columns using the alter table wizard?


... or I suppose you could drop all the columns after the intended insertion point, add the new column, then add all the other columns back. And then try to remember where you saved the data from the dropped columns - what? I was supposed to save that?

Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. B2SB - Saint Louis, MO - 63167
314-694-2592



<BR>_____________________________________________________________
<FONT size=2><BR>
DTCC DISCLAIMER: This email and any files transmitted with it are
confidential and intended solely for the use of the individual or
entity to whom they are addressed. If you have received this email
in error, please notify us immediately and delete the email and any
attachments from your system. The recipient should check this email
and any attachments for the presence of viruses.  The company
accepts no liability for any damage caused by any virus transmitted
by this email.</FONT>