Adding ID column to existing table

Question

Post reply

Adding ID column to existing table

Greg Snidow

SSCoach

Points: 16108
More actions
October 25, 2007 at 8:55 am

#75803

Greetings folks. I have a table I need to modify to allow duplicates of the primary key, so I am adding an identity column. I am wondering, what is the best way to do this. This is what I tried, and it worked, for the most part.
ALTER TABLE MyTable
ADD ID INT
SELECT
KeyCol,
ID = IDENTITY(INT,1,1)
INTO #Temp
FROM MyTable
UPDATE MyTable
SET id = t.id
FROM MyTable m,
#Temp t
WHERE m.KeyCol = t.KeyCol
My questions are, how do I change the int column to be identity, and how do I make the ordinal position of the id column to be 1. I realize I could right click on the first column from EM, and choose insert column, and choose yes for identity, but I want to do it without using EM. Any ideas?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

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

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

alter table mytable

add id int IDENTITY(1,1) not null

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

mrpolecat (10/25/2007)
alter table mytable
add id int IDENTITY(1,1) not null

Thanks mrpolecat. That worked, but I want the ID column to be the first column in the table. Is this possible with TSQL, or do I have to use EM and right click and insert column?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

Why do you care where it is? Data is displayed in the order you select it. I think when you do that through EM it causes the table to be dropped, reccreated and repopulated which is why it takes so long. You could of course do all of that through QA if you wanted to.

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

Well that answers my question then. Why do I care? I'm just like that. I want them all to be the same I guess. Thanks

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

In EM "Desing Table" there is an icon "Save change script".

After all changes you need to do are completed instead of saving table click on that icon.

Copy-paste to QA.

Enjoy.

_____________
Code for TallyGenerator

Greg Snidow SSCoach Points: 16108 More actions · Answer 6

Thanks Sergiy. I had no idea what was happening when you make changes in EM.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.