Setting Identity on column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Setting Identity on column

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-03-16 : 14:17:18

I have an [ID] column in a table which is a primary key,

[ID] INT NOT NULL

How do I set IDENTITY(1, 1) for this column.

I tried doing

ALTER TABLE LEGALCOR ALTER COLUMN ID INT NOT NULL

but it doesnt seem to work and it gives me an error.

Any suggestions in writing a DDL statement to do this.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-16 : 14:31:55

You can't use ALTER TABLE to modify a column to use IDENTITY. Do the change in Enterprise Manager and click the save change script to see what it takes to do this. It will use a temp table that matches your desired layout, copies the data into this new table, drops your table, renames the new table to your table, then adds indexes and constraints.

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 00:16:56

or

Drop that column and create new column with identity property

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-17 : 00:55:00

quote:

Drop that column and create new column with identity property

Yeah but that'll put it at the end of the table, which isn't typically where you want it. Identity columns are usually the primary key which are usually the first columns in the table. Plus you'll lose your data if you drop it then recreate it. I would go with the temp table approach that I suggested if I were you. Enterprise Manager produces the correct code for this.

Tara Kizer
aka tduggan

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-17 : 01:16:34

Refer to the Following link for setting the Identity Property for tables having records,

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62652

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 01:42:44

>>Enterprise Manager produces the correct code for this

Yes. Usually I do this in EM and advise others to do so as doing this in QA will lead in writing so many codes.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources