locked
How to alter column to identity(1,1) RRS feed

  • Question

  • I tried

     

    Alter table table1 alter column column1 Identity(1,1);

    Alter table table1 ADD CONSTRAINT column1_def Identity(1,1) FOR column1

     

    they all can not work,

    any idea about this? thanks

     

    Monday, June 18, 2007 11:21 AM

Answers

  •  

     

    You can't alter the existing columns for identity.

     

    You have 2 options,

    1. Create a new table with identity & drop the existing table

    2. Create a new column with identity & drop the existing column

     

    But take spl care when these columns have any constraints / relations.

     

     

    Code Snippet

    /*

                For already craeted table Names

                Drop table Names

                Create table Names

                (

                            ID int,

                            Name varchar(50)

                )

     

                Insert Into Names Values(1,'SQL Server')

                Insert Into Names Values(2,'ASP.NET')

                Insert Into Names Values(4,'C#')

    */

     

     

    Code Snippet

    --In this Approach you can retain the existing data values on the newly created identity column

    CREATE TABLE dbo.Tmp_Names

                (

                Id int NOT NULL IDENTITY (1, 1),

                Name varchar(50) NULL

                )  ON [PRIMARY]

     

    go

    SET IDENTITY_INSERT dbo.Tmp_Names ON

     

    go

    IF EXISTS(SELECT * FROM dbo.Names)

                INSERT INTO dbo.Tmp_Names (Id, Name)

                            SELECT Id, Name FROM dbo.Names TABLOCKX

     

    go

    SET IDENTITY_INSERT dbo.Tmp_Names OFF

     

    go

    DROP TABLE dbo.Names

     

    go

     

    Exec sp_rename 'Tmp_Names', 'Names'

     

     

    Code Snippet

    --In this approach you can’t retain the existing data values on the newly created identity column;

    --The identity column will hold the sequence of number

     

    Alter Table Names Add Id_new Int Identity(1,1)

    Go

     

    Alter Table Names Drop Column ID

    Go

     

    Exec sp_rename 'Names.Id_new', 'ID','Column'

     

     

     

    Monday, June 18, 2007 12:02 PM
  •  

    Code Snippet

    --create test table

    create table table1 (col1 int, col2 varchar(30))

    insert into table1 values (100, 'olddata')

     

     --add identity column

    alter table table1 add col3 int identity(1,1)

    GO

     

    --rename or remove old column

    exec sp_rename 'table1.col1', 'oldcol1', 'column'

    OR

    alter table table1 drop column col1

    --rename new column to old column name

    exec sp_rename 'table1.col3', 'col1', 'column'

    GO

     

    --add new test record and review table

    insert into table1 values ( 'newdata')

    select * from table1

     

    Monday, June 18, 2007 11:49 AM

All replies

  •  

    Code Snippet

    --create test table

    create table table1 (col1 int, col2 varchar(30))

    insert into table1 values (100, 'olddata')

     

     --add identity column

    alter table table1 add col3 int identity(1,1)

    GO

     

    --rename or remove old column

    exec sp_rename 'table1.col1', 'oldcol1', 'column'

    OR

    alter table table1 drop column col1

    --rename new column to old column name

    exec sp_rename 'table1.col3', 'col1', 'column'

    GO

     

    --add new test record and review table

    insert into table1 values ( 'newdata')

    select * from table1

     

    Monday, June 18, 2007 11:49 AM
  •  

     

    You can't alter the existing columns for identity.

     

    You have 2 options,

    1. Create a new table with identity & drop the existing table

    2. Create a new column with identity & drop the existing column

     

    But take spl care when these columns have any constraints / relations.

     

     

    Code Snippet

    /*

                For already craeted table Names

                Drop table Names

                Create table Names

                (

                            ID int,

                            Name varchar(50)

                )

     

                Insert Into Names Values(1,'SQL Server')

                Insert Into Names Values(2,'ASP.NET')

                Insert Into Names Values(4,'C#')

    */

     

     

    Code Snippet

    --In this Approach you can retain the existing data values on the newly created identity column

    CREATE TABLE dbo.Tmp_Names

                (

                Id int NOT NULL IDENTITY (1, 1),

                Name varchar(50) NULL

                )  ON [PRIMARY]

     

    go

    SET IDENTITY_INSERT dbo.Tmp_Names ON

     

    go

    IF EXISTS(SELECT * FROM dbo.Names)

                INSERT INTO dbo.Tmp_Names (Id, Name)

                            SELECT Id, Name FROM dbo.Names TABLOCKX

     

    go

    SET IDENTITY_INSERT dbo.Tmp_Names OFF

     

    go

    DROP TABLE dbo.Names

     

    go

     

    Exec sp_rename 'Tmp_Names', 'Names'

     

     

    Code Snippet

    --In this approach you can’t retain the existing data values on the newly created identity column;

    --The identity column will hold the sequence of number

     

    Alter Table Names Add Id_new Int Identity(1,1)

    Go

     

    Alter Table Names Drop Column ID

    Go

     

    Exec sp_rename 'Names.Id_new', 'ID','Column'

     

     

     

    Monday, June 18, 2007 12:02 PM
  • thank you

    but I could not change the existing number order and add identity

    any idea about this?

     

    Tuesday, June 19, 2007 2:19 AM
  • Nope.  There is no way to alter a column to have the identity property.  You will have to create a new table and insert into it if you want initial control over the values in the column.
    Tuesday, June 19, 2007 4:48 AM
  • If you have ms sql srvr mgmt studio you can do it.  You just have to do it manually, not in script or command form.

     

    connect to your db

    show the table/column names in object explorer window

    right click on column name

    select modify

    in column properties tab

       alter "identity specification"/"is identity" to YES

       set increment/seed properties as desired

     

    Note:  if you leave it at default 1,1 it will automatically insert the next row with the appropriate next value (not 1)

     

         Also, I had dup vals in the column but my primary key is a compound key with that and another field and I had no problem accessing the old data - just needed to keep the key with both fields.

     

    I hope this response helps and isnt too late... Jane. 

     

     

    • Proposed as answer by hugo.speculum Thursday, December 12, 2019 3:25 PM
    Tuesday, October 30, 2007 8:05 PM
  • In the management studio, drill down to the table and then to the column you want to change to identity. Right click on the column and select Modify option. At the bottom of the screen, you will find column properties. Scroll down tha list and you will see "Identity Specification". Expand that and change (Is Identity) to Yes and give in the Seed (just make sure it is after the latest value that you already have in the table just to avoid conflicts later on) and incremental values and save. That should do the trick.

     

    I had rows existing in my table and managed to keep them with the existing values. New rows loaded did have the id value automatically generated. Did not generate from the seed that I gave, but I am not sure if it was because of rows that had previously existed in the table that I had deleted earlier.

     

    Hope this helps.

    Friday, June 27, 2008 5:35 PM
  • You can do it manually through 'Management Studio' but there appears to be a bug which stops you from doing it through SQL. There is even Microsoft sample code:-

     

    CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))
    INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
    ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)

    which doesn't work on SQL Server 2005 queries.

    What you have to do is create a table with the 'identity' column, but with the 'Identity_Insert' property set so that you can put values into it. Unfortunately you must set the 'Identity_Insert' on the server itself (not a linked server) so that everything gets done in the same session. And you have to unset it before you can set it again, even for another table. Very shabby. And all because you can't do in this SQL implementation what you can do manually.

     

     

     

    Monday, August 4, 2008 9:11 AM
  • If by manually you mean using the Management Studio user interface then it's really automatically. Management studio will run a script that will create a new table, copy the data across for you and drop the original table. Unless you tick Prevent saving changes that require table re-creation in the Options, in which case it will refuse to make this change.
    Tuesday, January 26, 2010 11:42 AM
  • you do not need to drop the table or column. You have been given bogus misinformation, which is annoying appearing in SQL-Server Help

    I use  DBCC Checkident with reseed option to reset identity columns

    this example is in books online

    USE AdventureWorks;
    GO
    DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30);
    GO
    
    with this you can alter the column for identity.


    fa
    • Proposed as answer by BiGBoY9 Wednesday, July 4, 2012 8:36 AM
    Thursday, November 18, 2010 10:36 AM
  • you do not need to drop the table or column. You have been given bogus misinformation, which is annoying appearing in SQL-Server Help

    I use  DBCC Checkident with reseed option to reset identity columns

    this example is in books online

     

    USE AdventureWorks;
    GO
    DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30);
    GO
    
    with this you can alter the column for identity.

     


    fa

    @Farid... thats why they call half knowledge is dangerous thing.

    DBCC CHECKIDENT only applies to Identity columns, if a table have it. {MS BOLChecks the current identity value for the specified table in SQL Server 2008 R2 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.}

    Here the table does not contain any IDENTITY column and @Andre_s wants to change the column property to IDENTITY. Which is not possible by the ALTER statement.

    You would need to re-create table load the data again and drop the old table.

    OR... SSMS do this graphically, but internally it also recreate the table, load the data & drops the table.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Thursday, November 18, 2010 11:05 AM
  • Hi, I followed you code snippet #2, trying to retain existing data values for the new identity column, here is the error I got: "cannot insert explicit value for identity column in table "tableName" when Identity_insert is set to OFF. Thanks.

    Sarah Wang

    Wednesday, June 13, 2012 6:03 PM
  • The easiest way I have found to add an identity to an existing column (assuming you don't have direct access through SSMS) is to

    1. Script out the table definition to a query
    2. Rename the table in the script and add identity(1,1) to the column you need the identity on
    3. Rename the primary key (I usually use the same name and put a 2 behind it)
    4. Run the create table script

    (Note that the 4 steps above are prerequisites for this technique. Those are really simple steps, so I figured they didn't need much more explanation; however, the procedure that follows it to perform the switcheroo between the table that doesn't have an identity and the table that does can be a bit touchier)

    here comes the magic:

    --1.Disable all constraints
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
    --2."Switch TO" everything from the existing table to the new table
    ALTER TABLE [tablename] SWITCH TO [tablename2]
    --3.Drop the table that had no identity drop table [tablename]
    --4. Rename the table with new identity column to old table name sp_rename 'tablename2','tablename'

    --5. Rename pk to match old pk name sp_rename 'pk_tablename2','pk_tablename'
    --6. Re-enable all constraints exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"


    You'd be surprised how much more difficult it is to explain it than it is to actually do it.







    • Edited by K. Alan Bates Monday, April 20, 2015 5:24 PM NOTE1: If you have any foreign keys, you'll have to "fk_name2" them and then run them through sp_rename after the SWITCH TO| Note 2: Added step comments directly to code for legibility
    Wednesday, June 13, 2012 7:57 PM
  • Hi All,

    Another post for the same. Please try it.

    http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Monday, June 3, 2013 4:16 AM
  • Starting with SQL Server 2012 SEQUENCE object can be used instead of IDENTITY:

    CREATE SEQUENCE Sales.SalesOrderHeaderIdent
        START WITH 1
        INCREMENT BY 1 ;
    GO
    
    SELECT NEXT VALUE FOR Sales.SalesOrderHeaderIdent;
    GO 50


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016


    Monday, June 3, 2013 5:11 AM
  • Hi Toth,

    It is great. Here is the complete info about CREATE SEQUENCE in 2012

    http://msdn.microsoft.com/en-us/library/ff878058.aspx


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, June 5, 2013 12:46 AM
  • Hi Toth,

    I got one doubt: 

    If source is other than SQL Server & need to generate this sequence no then How can we do this in ETL? Please can you suggest if any work around?


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, June 5, 2013 1:45 AM
  • >If source is other than SQL Server & need to generate this sequence

    You import the source first into a SQL Server staging table:

    CREATE SEQUENCE NotSSSource
        START WITH 1
        INCREMENT BY 1 ;
    GO
    CREATE TABLE StageNotSSSource (
    ID INT DEFAULT (NEXT VALUE FOR NotSSSource),
    ProductID int,
    ListPrice smallmoney)
    GO
    INSERT StageNotSSSource (ProductID, ListPrice)
    SELECT ProductID, ListPrice 
    FROM AdventureWorks2012.Production.Product
    WHERE Color is NOT NULL;
    GO
    SELECT * FROM StageNotSSSource ORDER BY ID;
    GO
    /*
    ID	ProductID	ListPrice
    ....
    28	721	1489.3326
    29	722	350.8437
    30	723	350.8437
    31	724	350.8437
    32	725	350.8437
    33	726	350.8437  .... */


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016


    • Edited by Kalman Toth Wednesday, November 8, 2017 10:37 PM
    Friday, June 14, 2013 2:31 PM
  • ALTER TABLE table ADD column int NOT NULL IDENTITY (1, 1)
    Tuesday, July 30, 2013 3:01 PM
  • IDENTITY is a table property, not a column. It is also not relational and an SQL programmer would never use it. How can the count of physical insertion attempts to one table on one installation of one vendor's SQL product be part of a valid data model? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 30, 2013 3:29 PM
  • Basically there are four logical steps.

    1. Create a new Identity column. Turn on Insert Identity for this new column.

    2. Insert the data from the source column (the column you wished to convert to Identity) to this new column.

    3. Turn off the Insert Identity for the new column.

    4. Drop your source column & rename the new column to the name of the source column.

    There may be some more complexities like working across multiple servers etc.

    Please refer the following article for the steps (using ssms & T-sql). These steps are intended for beginners with less grip on T-SQL.

    http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

    Saturday, April 19, 2014 6:46 AM
  • An easier way is to Go to Tools>Designers>Table and Designers and uncheck "Prevent Saving Changes That Prevent Table Recreation."  You can always turn the function back on after you're happy with your table structures.

    Friday, August 15, 2014 10:57 PM
  • It looks like very nice solution for alter column as identity instead of creating a new table a d renaming.

    What's your thought?

    Friday, July 10, 2015 6:06 AM