Caveman's Blog

My commitment to learning.

SQL Server: How to add an identity column to a table with data.

with 12 comments


This article demonstrates a step-by step procedure about how an identity column an be added to a table that has data. There are two ways of adding an identity column to a table with existing data:

1. Create a new table with identity, copy data to this new table then drop the existing table followed by renaming the temp table.

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

With the second method of implementation the data of the intended identity column cannot be retained, hence we are only interested in the first method. Now lets take a look at how the script for Method 1 can be implemented.

if exists (select 1 from information_schema.tables where table_name = 'DataTable')
drop table DataTable
GO

Create TABLE DataTable(id int, name varchar(20) )
Insert into DataTable(id, name)
Values(1, 'test 0'),
(4, 'test 1'),
(5, 'test 2'),
(6, 'test 3')
GO

--Create a temp table with an identity column
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_DataTable
(
id int NOT NULL IDENTITY (1, 1),
name varchar(20) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_DataTable ON
GO
IF EXISTS (SELECT 1 FROM dbo.DataTable)
INSERT INTO dbo.Tmp_DataTable (id, name)
SELECT id, name FROM dbo.DataTable WITH (HOLDLOCK TABLOCKX)
GO
SET IDENTITY_INSERT dbo.Tmp_DataTable OFF
GO
DROP TABLE dbo.DataTable
GO
EXECUTE sp_rename N'dbo.Tmp_DataTable', N'DataTable', 'OBJECT'
GO
COMMIT

select * from DataTable

id name
——– ———-
1 test 0
4 test 1
5 test 2
6 test 3

Now you have a table with data that has an identity column. Notice that the data of an identity column does not have to be contiguous, thought it has to be in ascending order. Caution must be exercised here to drop and recreate all table constraints and relations.

Tip 1: how to reseed an identity column of a table to a new value

–Lets assume the table needs to be reseeded to a value of 100
Declare @newValue int
set @newValue = 100

DBCC CHECKIDENT (‘MyDataTable’, reseed, @newValue – 1)

Tip 2: how to reset the identity column of a table.

Delete from MyDataTable
DBCC CHECKIDENT (‘MyDataTable’, reseed, 0)

Tip 3: how to find the last inserted identity value.

SQL Server provides are three methods of fetching the last insere.ted identity value and all three methods have different scope with in a database operational context. I think Scope_Identity is the most preferred and secure method of fetching the identity valu

1. select @@IDENTITY returns the last identity value that was inserted into a table by you are by any other entity in the database scope. If there is no identity insertion @@IDENTITY returns NULL.

2. select SCOPE_IDENTITY() returns the last identity value that was inserted into a table by you or your connection in the database scope.

3. select IDENT_CURRENT(‘DataTable’) returns the last identity value of a given table.

Reference:
1. MSDN Online

kick it on DotNetKicks.com

Insert into DataTable(id, name)
Values(1, ‘test 0′),
(4, ‘test 1′),
(5, ‘test 2′),
(6, ‘test 3′)

About these ads

Written by cavemansblog

April 2, 2009 at 1:02 am

12 Responses

Subscribe to comments with RSS.

  1. GReat Solution

    Nitin

    April 9, 2010 at 2:21 pm

  2. thank to right code for set SET IDENTITY_INSERT.This solution is GReat Solution for update IDENTITY

    yashwant kumar

    July 26, 2010 at 5:22 am

  3. The second option works fine on a table which has data:
    ALTER TABLE ADD INT IDENTITY(1,1)

    Margaret

    June 5, 2011 at 12:21 pm

    • The second option does not work when you want to retain the original values of the identity column of the table where you are adding new data.

      cavemansblog

      June 5, 2011 at 10:05 pm

  4. Website…

    SQL Server: How to add an identity column to a table with data. « Caveman's Blog…

    Website

    July 28, 2011 at 5:48 pm

  5. All features of SQL Server IDENTITY property of columns. And a handy procedure to monitor all Identity columns. http://www.sqllion.com/2011/08/identity-in-sql/

    SQL Lion

    August 20, 2011 at 9:32 am

  6. Thank you so much for this solution.

    Divya

    September 26, 2011 at 3:45 am

  7. bloging…

    [...]SQL Server: How to add an identity column to a table with data. « Caveman's Blog[...]…

    bloging

    November 29, 2011 at 10:13 am

  8. Thanks for the solution.

    Deepthi

    March 5, 2012 at 4:57 pm

  9. thanks…
    so helpfull my jobs now

    ucul

    July 26, 2012 at 4:18 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: