Click here to Skip to main content
Click here to Skip to main content
Go to top

How to add an identity column to a table with data?

, 13 Jul 2012
Rate this:
Please Sign up or sign in to vote.
Add an identity column to a table with data

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

This will be the output of executing the above script:

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 value

  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. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sudheer Reddy Battula

United States United States
Sudheer Reddy Battula - Caveman's Blog

Comments and Discussions

 
Answerthis is easy PinmemberMember 96466309-Dec-13 19:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 13 Jul 2012
Article Copyright 2012 by Sudheer Reddy Battula
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid