Convert an existing Column to Identity

Question

I have a table in SQL Server with bundle of records. I want to convert the ID column which is Primary Key to an identity Column without loss of data. I thought of the following two approaches:

Create a new table with identity & drop the existing table.
Create a new column with identity & drop the existing column.

but it's clear that they can not be implemented because keeping records is my first priority.

Is there another way to do this?

possible duplicate of [Adding an identity to an existing column](http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) — juergen d, Oct 25 '13 at 08:06
Is your ID column already connected with other tables as foreign key? — kinske, Oct 25 '13 at 08:11
I can't see how keeping records is problem for any of two approaches. There are no other alternatives. You simply have to create something new, and if you want to keep IDs you already have - it's a new table. — Nenad Zivkovic, Oct 25 '13 at 08:33
@NenadZivkovic, because the asker tagged Sql Server 2012, there is an alternative. steoleary's answer will do it -- I've used that same solution myself in this exact situation. — Tim S, Oct 25 '13 at 13:46

kinske · Answer 1 · 2013-10-25T11:30:06.627

This solution violates your point 2, but there is no other way and I think your aim is to keep the old values, because nothing else makes sense...

You could do the following:

make it possible to insert into identity columns in your table:
```
set identity_insert YourTable ON
```
add a new ID column to your table with identity and insert the values from your old columns
turn identity insert off
```
set identity_insert YourTable OFF
```
delete old ID column
rename new column to old name
make it to the primary key

The only problem could be that you have your ID column already connected as foreign key to other tables. Then you have a problem with deleting the old column... In this case you have to drop the foreign key constraints on your ID column after step 3, then do step 4 to 6 and then recreate your foreign key constraints.

score 4 · Accepted Answer · answered Oct 25 '13 at 09:34

4

As you are using SQL Server 2012, another possible alternative could be to create a sequence object that has a starting value of the highest ID +1 already in your table, then create a default constraint for your column using GET NEXT VALUE FOR and reference your sequence object you just created.

answered Oct 25 '13 at 09:34

steoleary

8,968
2
33
47

I've had the exact same problem and did this to solve it. Worked great. – Tim S Oct 25 '13 at 13:43
1

Dang. I meant to include an example: http://stackoverflow.com/questions/14413383/sequence-as-default-value-for-a-column – Tim S Oct 25 '13 at 13:52
What do you do in case you have a guid / uniqueidentifier? – Alexey Shevelyov Mar 18 '18 at 08:28

score 3 · Answer 3 · answered Aug 30 '16 at 20:33

If you have direct access to the Server Database, just go into the design of the table, select the PK column, and change the identity to "Yes". Make sure you set your seed to the max value of that column. The increment is 1 by default. Save the table design and you should be good to go.

score 0 · Answer 4 · answered Feb 26 '19 at 16:15

Considering the source table isn't too big:

Create new table (with IDENTITY)
Populate new table from existing table (with IDENTITY_INSERT ON)
Drop old table (drop any existing FKs first)

Rename new table to old name (re-establish FKs if needed)

   -- Create Sample Existing Table
   DROP TABLE IF EXISTS #tblTest
   CREATE TABLE #tblTest
   (
        ID      INT NOT NULL
        , Val   VARCHAR(10) NOT NULL
    )

    INSERT INTO #tblTest 
    (
        ID
        , Val
    )
    VALUES 
        (1, 'a')
        , (2, 'b')
        , (4, 'c')
    GO

    -- Create and Populate New Table (with IDENTITY_INSERT ON)
    DROP TABLE IF EXISTS #tblTestNew
    CREATE TABLE #tblTestNew
    (
        ID      INT IDENTITY(1, 1) NOT NULL 
        , Val   VARCHAR(10) NOT NULL
    )

    SET IDENTITY_INSERT #tblTestNew ON

    INSERT INTO #tblTestNew 
    (
        ID
        , Val
    )
    (
        SELECT
            #tblTest.ID
            , #tblTest.Val
        FROM
            #tblTest
    )

    SET IDENTITY_INSERT #tblTestNew OFF
    GO

    -- Rename Existing Table to Old (can use sp_rename instead, but I can't for temp tables)
    SELECT * INTO #tblTestOld FROM #tblTest
    DROP TABLE #tblTest
    GO

    -- Rename New Table to Existing (can use sp_rename instead, but I can't for temp tables)
    SELECT * INTO #tblTest FROM #tblTestNew
    DROP TABLE #tblTestNew
    GO

    -- Test Inserting new record
    INSERT INTO #tblTest (Val)
    VALUES ('d')

    -- Verify Results
    SELECT * FROM #tblTest
    EXEC tempdb.sys.sp_help @objname = N'#tblTest'

    --  Drop 'Old' Table (when ready)
    DROP TABLE IF EXISTS #tblTestOld

    -- Cleanup
    DROP TABLE IF EXISTS #tblTest
    DROP TABLE IF EXISTS #tblTestNew
    DROP TABLE IF EXISTS #tblTestOld

If the table is very large, consider the log growth, Recovery Model, possible single-user mode, etc.

vhadalgi · Answer 5 · 2013-10-25T09:01:52.123

-2

create table t1 (col1 int, col2 varchar(10))

insert into t1 values (10, 'olddata')

--add identity col

alter table t1 add col3 int identity(1,1)

GO

--rename or remove old column

alter table t1 drop column col1

--rename new col to old col name

exec sp_rename 't1.col3', 'col1', 'column'

GO

--add new test , review table

insert into t1 values ( 'newdata')

select * from t1

edited Oct 25 '13 at 09:01

answered Oct 25 '13 at 08:50

vhadalgi

7,027
6
38
67

Convert an existing Column to Identity

5 Answers5