Question: Is it possible to add an auto incremental identity column to any table in SQL Server after creating a table.
Answer: There are two answers – No and Yes. Let us see them one by one.
Answer No – If you have an integer column in your table and you want to convert that column to identity table. It is not possible with the help of SQL Server. You will just have to add a new column.
Answer Yes – If you want to add new column to the table, it is totally possible to do so with the help of following a script.
ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)
If you want to convert your old column to int column, may be you can drop that first and add a new column identity right after that with the help of following a script.
ALTER TABLE YourTable DROP COLUMN IDCol ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)
Let me know if you have any other work around besides SSMS (as that option just drops table and recreates it).
Reference: Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
This can be done either by re-creating the table with the existing int column as identity + insert the data there and rename it to replace the original table, or use bcp out + truncate the table + change the field to identity + bcp in the data. Both of these require to use identity insert on.
ALTER TABLE YourTable ADD IDCol2 INT IDENTITY(1,1)
SET IDENTITY_INSERT YourTable ON
UPDATE YourTable SET IDCol2 = IDCol
SET IDENTITY_INSERT YourTable OFF
ALTER TABLE YourTable DROP COLUMN IDCol
EXEC sp_RENAME ‘YourTable.IDCol2’, ‘IDCol’, ‘COLUMN’
— To replace the values you had —
Thanks Robb…much better and complete solution. Keep in mind however that if the column being dropped has default values or indexes attached to it, they need to be dropped first and recreated for the new column
Very nice observation Kleidi.
I have written about adding IDENTITY columns to existing tables in 2 parts here: Part01 [http://beyondrelational.com/modules/2/blogs/77/posts/19516/0272-sql-server-identity-columns-myths-identity-columns-cannot-be-added-to-existing-tables-part-01.aspx] and Part 02 [http://beyondrelational.com/modules/2/blogs/77/posts/19518/0273-sql-server-identity-columns-myths-identity-columns-cannot-be-added-to-existing-tables-part-02.aspx]
Great Pinal Love your posts
Can we have auto increment when we have data already in tables ?
Did you read nakul’s post which he mentioned in earlier comment?
How to auto-increment something like: 001,0004,0009,0011,0018…!!!!?
What’s the logic?
if an auto incremented columns limit has exceed. what we have to do? because it will error, when you trying to insert new row.
IN MYSQL(PHPmyAdmin) ,goto Struture menu and checkbox column from right and then Click on change from bottom buttons .You will be redirected with changing options.Change the column accordingly.
Thanks for sharing this Tahir.
how to set auto increment to column which is int datatype and primary key constraint
I guess, you need create new table with the required schema, move the data and rename them.