March 13, 2002 at 12:21 pm
can I issue an alter table/column statement the cahnges an existing int to an identity column?
March 13, 2002 at 12:32 pm
no, you have to rebuild the table. If you do this in EM, that is what get's run.
Steve Jones
March 13, 2002 at 12:44 pm
I'm sure this is a common problem and hopefully there is a common fix.
We are runing a conversion and I want to maintain the id's from the source table, but once the conversion is finished we need the column to be an identity and increment by 1.
any suggestions?
Steve - thanks again.
-Matt
March 13, 2002 at 1:05 pm
No what Steve was saying is that you cannot use a TSQL statement to do this, you have to go in EM and turn it own, then EM will rebuild the table and insert all the original values (as long as they a unique, will then give an error) and you will have it done.
Thru TSQL you would rename the table, create a new table with the IDENTITY option turned on, and then use
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } ON
Insert you old data into the table then do
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } OFF
thus allowing you to fill in you old values. Before doing I always suggest making a file backup.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 13, 2002 at 1:11 pm
thank you.... that's what I needed to know.
March 13, 2002 at 1:51 pm
be sure when you do this that you set the seed correctly to start with the next value you want to use.
Steve Jones
December 8, 2002 at 8:43 pm
quote:
can I issue an alter table/column statement the cahnges an existing int to an identity column?
You can can add the new IDENTITY column and it will assigne new values for each row.
Then you can:
set identity_insert <your table> on
update <your table>
set NewIDColumn=YourOldKeyColumn
set identity_insert <your table> off
and then delete YourOldKeyColumn from the table.
Afterward run DBCC CHECKIDENT for the table to reset the "next" value correctly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply