Enable an identity column that already has data in it?

Question

Someone disabled the identity column of a table in a SQL DB. Is it possible to re-enable that feature of the column even when there is already data in the table? And maintain the existing identity values?

I know I could copy the data to another table and reinsert it after setting Identity_Insert on.

Answer 1

You cannot switch on the IDENTITY on an existing column, that's just not possible in SQL Server right now (at least up to version 2012).

What you need to do is exactly what you describe:

• create the new table in the structure you want, with the IDENTITY column
• copy the data from the existing table into the new table, with SET IDENTITY_INSERT ON
• drop the old table
• rename the new table to the old table name

You can "re-enable" the identity specification in the visual table designer in SQL Server Mgmt Studio, but this really only does those above steps in the background, for you.

Answer 2

Just as *marc_s* said you can use

I dont know if there is any other way to do this but you can use

    CREATE TABLE tblNewTable
    (
         //Put the columns and datatypes of the former table
    )

    INSERT INTO tblNewTable
    AS
    SELECT * FROM oldTable

Then drop the table usng

    DROP TABLE oldTable

Then recreate the new table and add the identity column, then use

   INSERT INTO tblNewRecreatedTable (//Columns of the new created table except the column with the identity
   AS
   SELECT //Columns of the table you copied the data to except the Columned that you  defined identity

I hope it helps