IDENTITY COLUMN

[sparkme]

Hi,
There is a TABLE called PERSON ,A column called Person_id is primary key column in that with data.

Can some one tell me how to alter this column to be
a Identity column ,Basicaly a I'am looking for ALTER SCRIPT.

Thanks

 

[ESquared]

Go into Enterprise Manager.
Go into design mode and change the table how you want.
Click the "save change script" button (a yellow scroll paper thingie with a disk in front of it).
Voilá, you have an alter script. (Look in the resulting script for the ALTER section.)

All objects can be scripted in this way... now you never have to ask for syntax help again, just create the object and script it.

 

[sparkme]

Hi ESquared ,
Thanks .But the script generated from there drop and recreate the table . I dont want to drop a table because i will lose data.


So i need only just alter script
to change a column from ordinary to a column with primary key having IDENTITY

 

[JayKusch]

You cannot accomplish this using a T-SQL script. What you will have to do is what Enterprise Manager does when you add an Identity to a pre-existing field. Lets say our table is called TableA and FieldA needs to be set as an Identity field. We have data in table A so here is what we have to do ...

First rename TableA to say TableB

Create a new TableA WITH FieldA set as an Identity field.

Import data from TableB into TableA

DELETE TableB ... Once you are sure the new TableA meets your needs.



Thanks

J. Kusch

 

[maswien]

If you don't care lose data in the previous primary column and just want replace it with an identity column, then following is the solution:

1. alter table person drop column person_id

2. alter table person add person_id int identity(1,1)

 

[sparkme]

Thank you so much!