Alter Table add Column - How do you add a column after say the second column

When you use "Alter Table add Column", it adds the column to the end of the list of fields.

How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?

Create table T1 ( a varchar(20), b varchar(20), c varchar(20))

Alter table add column x varchar(20)

so that the resulting table is

T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)

Can this be done programmatically?

One option is to do the following (but it might take a while if you have lots of data):

- insert all data in a temp table

- drop the original table

- create a table with the new column in place

- insert all the data from the temp table in the new table

Make sure you take into account all constraints, indexes, triggers, ...

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Order of columns is irrelevant in a table. You can retrieve columns in a specific order when you query the table. It doesn't have to be physically added in the same order and the internal row format also doesn't store it in the order you specified in the CREATE TABLE. So why do you care where the column is in the CREATE TABLE? What are you trying to do with that information?|||

I was under the impression some SQL scripts would break. Am I mistaken?

insert into T1 select * from T2 ( I do run into these SQL scripts)

How does the Enterprise Manager insert a column into the table without having to drop the table and re-creating it?

Thanks.

|||

If you alter either T1 or T2, then you need to address the query anyway.

Enterprise manager copies the data out, recreates the table with the column in the position you designated, then copies the data back in....much as was described in an earlier post.

|||

I was hoping that was able to run a script that would be able to add a column (let's say column in the second position)

to both T1 and T2 so that I wouldn't have to searching for all the previous scripts that would break.

When you do use a script to Alter a table the column is added to the end. I end up having to go to the Enterprise Manager and drag and drop the column that is at the end of the table to the 2nd position.

I don't think the Enterprise Manager is recreating the table at this time. It should be simply moving the pointers in syscolumns for instance. I am just guessing, but that would be more` efficient.

|||

Then you are quite 'wrong' with your 'guess'.

Enterprise Managler most definitely

creates a new table with the column order you desire,

transfers all the data to the new table,

scripts out and adds the necessary constraints,

drops the old table,

and then renames the new table to the same name as the old table.