Autoincrement existing column sql server

Question

Hi everyone,

I am having hard time trying to make a primary key column which is of int type to auto-increment.Is their anyway possible to do it using script.I know it can be done using SQL Management Studio but i couldn't find the way to do it using script.Whenever i run the queries which has identity(1,1) identifier in the queries, it fails with the following message

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'identity'.

Answer 1

Read this thread..

Answer 2

you cannot alter the column to identity column, you can add a new column and drop the old one. I think this is what happens even in SSMS alter table TableA ADD newColumnName int NOT NULL identity(1,1)

---

Hope it Helps!!

Answer 3

Since you don't show the code that produces the error, it is not really clear to me what you are doing, and I will have to guess.

My guess is that you're question really is: How do I use ALTER TABLE to add the IDENTITY property to an existing column?

The answer is that you cannot use ALTER TABLE to neither add, nor drop the IDENTITY property. (Unless you are using SQL Server Compact Edition, which has this feature.)

What SSMS does behind the scenes is to create a new table and copy data over. And very unfortunately, the way it does it is not realiable, and if something goes wrong in the process you may lose constraints in the database.

If you are on SQL 2012, there is an alternate solution which is straightforward: create a sequence, call it Seq, and then do:

ALTER TABLE tbl  ADD CONSTRAINT tbl_autoincr DEFAULT (NEXT VALUE FOR Seq) FOR pkcol

On SQL 2008 and earlier, or if you absolutely must use IDENTITY, the situation is certainly dire. Create a new copy of the table, and don't forget indexes, triggers, constraints and referencing FKs.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Please read Erlands warnings about indexes, triggers, constraints and other keys before attempting this on your Prod server because changing the data or renaming the columns can break referential integrity of the data.

Various methods to add IDENTITY to a table are -

-- Example 1 - Simple IDENTITY column that defalts to begin from 1 and increment by 1
create table newtable1
(
id int identity, 
col1 varchar(10)
)

-- Example 2 - Explicitly beginning IDENTITY values from 10 and increment by 2
create table newtable2
(
id int identity(10,2),
col1 varchar(10)
)

-- Example 3 -  Adding IDNETITY column later
create table newtable3
(
id int,
col1 varchar(10)
)
insert into newtable3 (id, col1)
values (5, 'a'), (2, 'b')
select * from newtable3

-- add a new column
alter table newtable3
add id2 int identity 

-- and drop your old column
alter table newtable3
drop column id 

-- rename the column
exec sp_rename @objname = 'newtable3.id2', @newname = 'id', @objtype = 'column'

insert into newtable3 (col1) 
values ('c')
select * from newtable3

-- Example 4 -- Moving data to a separate table that has the IDENTITY already defined
-- Create a new table as in example 1 or 2 and move the data to that table.
insert into newtable1 (col1)
select col1 from newtable3

-- verify
select * from newtable1

-- Drop the old table
drop table newtable3

-- Rename the new table as the old table
exec sp_rename @objname = 'dbo.newtable1', @newname = 'newtable3'

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)