Auto Increment field value by using UPDATE statement.

When creating tables in SQL Server we can specify particular field to be auto incremented, specifically the primary key field. For this purpose MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. By default, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

But if you need to change the value to be started with a specific value, then you can just simply specify as below:

IDENTITY(10,5)

in this case the value will start from 10 and incremented by 5 each time when a record is being getting inserted in to the table.

Ex:

CREATE TABLE Persons(
EmployeeId int PRIMARY KEY IDENTITY(10,5),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255))

I was in a situation where i had to do the same thing as mentioned above to an existing table where do some data exist.

What i did was just inserted the records to the table except the primary key. It means temporarily i just allowed NULL to be inserted to the primary key column. After that just update that primary key values by simply running a update statement as below setting the value the primary key should start with.

DECLARE @counter int
SET @counter = 50
UPDATE [Test].[dbo].[Test]
SET @counter = [EmployeeId] = @counter + 1
Advertisements

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

%d bloggers like this: