SQL Server How to set auto increment after creating a table without data loss

Question

I have a table table1 in SQL server 2008 and it has records in it    I want the primary key table1 Sno column to be an auto-incrementing column   Can this be done without any data transfer or cloning of table   I know that I can use ALTER TABLE to add an auto-increment column  but can I simply add the AUTO INCREMENT option to an existing column that is the primary key

User · Answer

If you want to do this via the designer you can do it by following the instructions here  quot Save changes is not permitted quot  when changing an existing column to be nullable

User · Answer

SQL Server  How to set auto-increment on a table with rows in it   This strategy physically copies the rows around twice which can take a much longer time if the table you are copying is very large    You could save out your data  drop and rebuild the table with the auto-increment and primary key  then load the data back in     I ll walk you through with an example   Step 1  create table foobar  without primary key or auto-increment    CREATE TABLE foobar      id int NOT NULL      name nchar 100  NOT NULL      Step 2  insert some rows  insert into foobar values 1   one    insert into foobar values 2   two    insert into foobar values 3   three      Step 3  copy out foobar data into a temp table   select   into temp foobar from foobar   Step 4  drop table foobar   drop table foobar    Step 5  recreate your table with the primary key and auto-increment properties   CREATE TABLE foobar      id int primary key IDENTITY 1  1  NOT NULL      name nchar 100  NOT NULL      Step 6  insert your data from temp table back into foobar  SET IDENTITY INSERT temp foobar ON INSERT into foobar  id  name  select id  name from temp foobar    Step 7  drop your temp table  and check to see if it worked   drop table temp foobar  select   from foobar    You should get this  and when you inspect the foobar table  the id column is auto-increment of 1 and id is a primary key   1    one 2    two 3    three

User · Answer

No  you can not add an auto increment option to an existing column with data  I think the option which you mentioned is the best   Have a look here

User · Answer

Yes  you can   Go to Tools   Designers   Table and Designers and uncheck  Prevent Saving Changes That Prevent Table Recreation

User · Answer

If you don t want to add a new column  and you can guarantee that your current int column is unique  you could select all of the data out into a temporary table  drop the table and recreate with the IDENTITY column specified  Then using SET IDENTITY INSERT ON you can insert all of your data in the temporary table into the new table

User · Answer

Changing the IDENTITY property is really a metadata only change   But to update the metadata directly requires starting the instance in single user mode and messing around with some columns in sys syscolpars and is undocumented unsupported and not something I would recommend or will give any additional details about   For people coming across this answer on SQL Server 2012  by far the easiest way of achieving this result of an auto incrementing column would be to create a SEQUENCE object and set the next value for seq as the column default   Alternatively  or for previous versions  from 2005 onwards   the workaround posted on this connect item shows a completely supported way of doing this without any need for size of data operations using ALTER TABLE   SWITCH  Also blogged about on MSDN here  Though the code to achieve this is not very simple and there are restrictions - such as the table being changed can t be the target of a foreign key constraint   Example code   Set up test table with no identity column   CREATE TABLE dbo tblFoo    bar INT PRIMARY KEY  filler CHAR 8000   filler2 CHAR 49      INSERT INTO dbo tblFoo  bar  SELECT TOP  10000  ROW NUMBER   OVER  ORDER BY  SELECT 0   FROM master  spt values v1  master  spt values v2   Alter it to have an identity column  more or less instant    BEGIN TRY      BEGIN TRANSACTION         Using DBCC CHECKIDENT  dbo tblFoo   is slow so use dynamic SQL to       set the correct seed in the table definition instead       DECLARE  TableScript nvarchar max      SELECT  TableScript              CREATE TABLE dbo Destination          bar INT IDENTITY                           CAST ISNULL MAX bar  0  1 AS VARCHAR      1   PRIMARY KEY          filler CHAR 8000           filler2 CHAR 49                     ALTER TABLE dbo tblFoo SWITCH TO dbo Destination                   FROM dbo tblFoo     WITH  TABLOCKX HOLDLOCK       EXEC  TableScript        DROP TABLE dbo tblFoo       EXECUTE sp rename N dbo Destination   N tblFoo    OBJECT         COMMIT TRANSACTION  END TRY BEGIN CATCH     IF XACT STATE    lt  gt  0 ROLLBACK TRANSACTION      PRINT ERROR MESSAGE    END CATCH    Test the result   INSERT INTO dbo tblFoo  filler filler2   OUTPUT inserted   VALUES   foo   bar     Gives  bar         filler    filler2 ----------- --------- --------- 10001       foo       bar         Clean up  DROP TABLE dbo tblFoo

User · Answer

Below script can be a good solution Worked in large data as well   ALTER DATABASE WMlive SET RECOVERY SIMPLE WITH NO WAIT ALTER TABLE WMBOMTABLE DROP CONSTRAINT PK WMBomTable ALTER TABLE WMBOMTABLE drop column BOMID ALTER TABLE WMBOMTABLE ADD BomID int IDENTITY 1  1  NOT NULL  ALTER TABLE WMBOMTABLE ADD CONSTRAINT PK WMBomTable PRIMARY KEY CLUSTERED  BomID   ALTER DATABASE WMlive SET RECOVERY FULL WITH NO WAIT

[sql-server] SQL Server, How to set auto increment after creating a table without data loss?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to identity

Examples related to auto-increment