Adding a column to a specific place...

Question

Post reply

Adding a column to a specific place...

FreeHansje

SSChampion

Points: 11751
More actions
July 20, 2005 at 8:20 am

#66463

Question: can I add a column at a specific place in a table? When using t-sql the column is added as the last column, however, using EM I can add a column at a specific place. I imagine a rename, create,transfer and delete action is performed, I am hoping that this can be automated in t-sql.
Is this so, or do I have to create such a function myself?
Greetz,
Hans Brouwer

Viewing 7 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Use EM if you really have to, but check out this thread first and maybe you'll not worry about it

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=98351

Doing this in T-SQL is non-trivial and not recommended.

DCPeterson SSCoach Points: 15266 More actions · Answer 2

Actually, trying to insert a column in a specific order is indicative of a problem. There should be no meaningful ordering of either columns or rows in a relational table.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

FreeHansje SSChampion Points: 11751 More actions · Answer 3

Tnx for the response. I am aware that columnorder is not that important, although the fixed-length-first is new for me. Wanting to place the column in a specific place is for the convenience of humans, developers and production-dba's in this case.

But I understand the problems involved, tnx for the link. Funny thing: I search the forum for ALTER TABLE and got no hits...

Tnx anyway,

Greetz,
Hans Brouwer

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

By using alter table you cannot placethe new column in a particular position, but there is a workaround (how EM is doing as well) you create a new table with the desired structure, you copy all the data, drop the old table, rename the new table to the old name recreate all the objects linked to that table (keys, indexes, triggers...).

But much easier is to do that with EM.

Bye
Gabor

Ray M SSC-Insane Points: 21093 More actions · Answer 5

Like stated colorder does not matter, but if you must.

Using EM, Edit table in Design Mode, Add column where you want it.

Instead of hitting save click the Save changes to script Icon. It will generate a change script for You. Beware the script generator puts all kinds of extra junk in the script. and if your running this script on multiple servers and you are using default constraints without explicitly naming them, then the default constraints names in the script will be different on other servers. Causes a little extra pain.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

There's a big gotcha lurking in the table-rebuild script generated by Enterprise Manager. It uses transactions, but I believe the whole thing is running in some outer transaction that is not in the script.

If you read the script closely, it will drop your original table whether or not the data copy succeeds.

I usually modify it with something like IF (SELECT COUNT(*) FROM origtable) = (SELECT COUNT(*) FROM newtable) before the deletion, and take out the GO statements so it won't continue after an error.

Or only run the script down to the data copy, then verify the new table is good before running the rest. Or change the DROP TABLE to a rename.

You could use an extra BEGIN TRAN at the beginning, but I don't like transactions that big (for a large table) that stay open for an indefinate time (however long it takes you to decide it worked).