February 9, 2017 at 4:01 am
Hi all,
Kindly suggest me , how we can add identity to the existing column
I had tried may ways from google but it's not working all the time I had rename and drop column or table but its not beneficial
If anyone knows regrading this, share the details
February 9, 2017 at 4:09 am
There's no way round it, I'm afraid. You have to drop the column and recreate it with an identity property.
John
February 9, 2017 at 8:34 am
it may make more sense to rename the current column, create the new identity column, set the IDENTITY_INSERT On for the table, UPDATE the new identity column from the renamed column, then drop the renamed column.
March 22, 2017 at 10:32 am
ALTER TABLE [dbo].[TBL_NNN](
[ID] [int] IDENTITY(1,1) NOT NULL,
)
GO
_______________
March 23, 2017 at 12:47 am
SQLDave2015 - Wednesday, March 22, 2017 10:32 AMALTER TABLE [dbo].[TBL_NNN](
[ID] [int] IDENTITY(1,1) NOT NULL,
)
GO
_______________
You seem to have simply replaced the "CREATE" with "ALTER" in a CREATE TABLE script.
This will not work and will error out as its not valid syntax for ALTER TABLE statement.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 23, 2017 at 3:26 am
If you are on at least SQL2012, I suspect the easiest approach would be to create a sequence and then add that sequence as the default constraint on the column.
As long as you do not specify the column in the insert list the column will take the next value in the sequence on insert.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply