April 9, 2012 at 7:16 am
Hi,
Is there any way by which i can convert any column into an identity in sql server 2000,I can add an extra column as an identity but i am not able to convert an existing column into an identity.
Please help..
April 9, 2012 at 7:26 am
You have to drop and re-create the column, which really means you need to drop and re-create the table. You do something like this:
Create TABLE new_table_with_identity(columns...)
SET IDENTITY_INSERT new_table_with_identity ON
Insert Into new_table_with_identity Select * From old_table
SET IDENTITY_INSERT new_table_with_identity OFF
DROP foreign_key's and indexes on or referencing old_table
EXEC sp_rename 'old_table', 'old_table_old'
EXEC sp_rename 'new_table_with_identity', 'old_table'
Add foreign key's that reference the table
Add foreign key's to the table
Add indexes
Obviously test thoroughly on a test system.
The easiest way is to use SSMS to generate the create table script to create the new table from the old table just adding the identity property to the column you want the identity on.
I like to wrap the whole script in a transaction and I just rename the existing table and would drop it later, once I'm confident everything is working as designed.
The key parts are making sure you get all the relationships restored to the newly created table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2012 at 7:55 am
Thanks a ton Jack,
This is what i am looking for....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply