March 25, 2009 at 2:31 pm
Can IDENTITY be assigned to an existing column? Or can it only be used when creating from scratch?
I have a table with data imported from a spreadsheet. The Spreadsheet had a sequence column in it starting at 1 and incrementing by 1 just like an identity column would do.
Using ALTER TABLE, can I change the existing column to an identity column? Or do I have to drop the current column and add a new column?
I've looked at BOL and I'm still getting the hang of deciphering the format. In the ALTER TABLE section it mentions that if you're adding a column with IDENTITY that the data type needs to be a data type that can work with IDENTITY but it doesn't really say if you can or cannot add IDENTITY to an existing column.
I just want to make sure I'm actually reading BOL correctly.
-- Kit
March 25, 2009 at 2:42 pm
You could ALTER the table ADD a new column and SET IDENTITY, I don't think you can convert your current column to IDENTITY.
Kind regards,
Gift Peddie
March 25, 2009 at 2:44 pm
You could add an identity column, set identity insert on, and update to get the same value of your "Current ID" Column, then drop your column. You would then have a new Identity column, with the same values.
Cheers,
J-F
March 25, 2009 at 2:45 pm
Damn, Gift beat me to it, 😀
Cheers,
J-F
March 25, 2009 at 3:05 pm
Cool. Thanks. 🙂
Guess I'm starting to understand this BOL thingie.
I added the column and dropped the inserted column and it worked just fine. Got the same identity values to match my imported column values. Thanks again.
-- Kit
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply