Post reply

sql to add identity to an existing column

  • October 5, 2012 at 2:45 pm

    #262636

    Is there a way using SQl to add Identity (500,1) to an existing column?

    For existing records, keep what they are , but for new records start from 500?

    Thanks

  • October 5, 2012 at 3:00 pm

    #1546327

    Hi,

    If your column is already an Identity Column :

    DBCC CHECKIDENT (YourTable, RESEED, 499)

    Jeff.

  • October 5, 2012 at 3:07 pm

    #1546328

    Thanks, But how about the column was not identity before?

  • October 5, 2012 at 3:13 pm

    #1546331

    Something like that must work 🙂

    CREATE TABLE dbo.YourTableTemp (column_a INT IDENTITY(500,1),column_b NVARCHAR(25) ) ;

    SET IDENTITY_INSERT YourTableTemp ON

    INSERT INTO YourTableTemp (column_a,column_b)

    SELECT column_a,column_b FROM YourTable

    SET IDENTITY_INSERT YourTableTemp OFF

    EXEC sp_rename 'YourTable','YourTableOld'

    EXEC sp_rename 'YourTableTemp','YourTable'

    Jeff.

  • October 5, 2012 at 3:15 pm

    #1546332

    What I've done in the past is to

    1. Add a new Column with IDENTITY (1, 1) to the table

    2. turn off Identity INSERT

    3. copy the values from the existing column into the identity column

    a. it's been so long I don't remember if this step worked, I might have had to update all other references to this in other tables with a simple old/new query

    4. drop the old column

    5. rename the new column to the old column's name

    6. Reseed the column starting at your new value.

  • October 5, 2012 at 3:52 pm

    #1546339

    sqlfriends (10/5/2012)

    Is there a way using SQl to add Identity (500,1) to an existing column?

    For existing records, keep what they are , but for new records start from 500?

    Thanks

    You cannot change an existing column to identity.

    If you need to, you will have to create a new version of the table and load your existing data.

    In SSMS, if you change the identity attribute of a column, it will generate a script to do this behind the scenes. This may be the easiest way to get this done, but you should be aware of what is actually happening, especially if the table is large.

  • October 5, 2012 at 3:59 pm

    #1546340

    Thanks, that helps.

    Can I do the below too?

    I can create a new table and with the column set up with identity (500, 1).

    Then I insert the records that existed before with SET IDENTITY_INSERT on.

    , those records were created not using identity column before.

    After this is done,

    Then insert new records , which it is supposed to start from 501

    Thanks

  • October 6, 2012 at 2:26 am

    #1546391

    Yes you can do that with the script above.

    Jeff.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply