Friday, February 01, 2013 10:32 PM
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'.
Friday, February 01, 2013 10:53 PM
Friday, February 01, 2013 10:54 PMyou 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!!
Friday, February 01, 2013 11:05 PM
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, firstname.lastname@example.org
Saturday, February 02, 2013 12:11 AM
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.
Aalam | Blog (http://aalamrangi.wordpress.com)
- Marked As Answer by JollyWagoner Monday, February 04, 2013 11:15 PM