Adding new columns in an existing table at a particular position using script.

Question

Adding new columns in an existing table at a particular position using script.

Joy Smith San

SSC-Insane

Points: 24877
More actions
January 26, 2009 at 10:43 pm

#199288

Dear All,
Is it possible to add new columns in an existing table using script at a particular position ?.
I mean, When we execute the command
ALTERTABLE tablename
ADDInvoiceAmtNumeric(18,2)
it adds the 'InvoiceAmt' column at the end. I want it somewhere in the middle (immediately after invoiceNo. column).
Is it possible using script..?
Thanks in advance
Santhu.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First question, why? The position of the data in the row is immaterial. Second, using a script no. You'd have to create a new table with the data columns in the "order" you desired. Copy the data from the old table to the new table. Drop the old table. And then rename the new table to the name of the old table.

And if you have DRI, that makes it more difficult.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Fine..Last 2 fields of all the tables are common fields (Createddate and Isdeleted). thought I will keep that uniformity. Now I can' drop the tables..because system is live and I send only script to client.

Anyways thanks for your reply. Doesn't matter as long as it doesn't affect the system.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

nairsanthu1977 (1/27/2009)
Fine..Last 2 fields of all the tables are common fields (Createddate and Isdeleted). thought I will keep that uniformity. Now I can' drop the tables..because system is live and I send only script to client.
Anyways thanks for your reply. Doesn't matter as long as it doesn't affect the system.
Thanks.

Thank you for the feedback. I understand that it would be nice to keep those standard (common) columns at the end. If you still wish to do that, you just have to do more work, and schedule some system down time when adding the new columns.

An aside, this is a good reason to actually place standard columns at the "front" of the rows. This way, as columns are added or deleted, you don't affect those columns. Something for DB Designers to keep in mind as they design databases. It makes things look uniform and consistant. The bottom line, however, is that in a RDBMS like SQL Server, positioning in the row really doesn't matter.