Sql Server: Insert Explicit Value for Identity Column in Table


Explicit value can be inserted for indenity column in a table. It is possible by SET IDENTITY_INSERT tablename ON. In following example “HumanResources.Department” is used. In this table DepartmentID is an identity column.
USE AdventureWorks
GO
— check already inserted records in table
SELECT  *
FROM    HumanResources.Department
GO
— for explicit insert use IDENTITY_INSERT — ON
SET IDENTITY_INSERT HumanResources.Department ON
INSERT  INTO HumanResources.Department
        (DepartmentID,Name,GroupName)
        SELECT  55,‘TEST’,‘TEST’
GO
— to resume implicit insert in identity column use IDENTITY_INSERT — OFF
SET IDENTITY_INSERT HumanResources.Department OFF
INSERT  INTO HumanResources.Department ( Name, GroupName )
        SELECT  ‘TEST2’,‘TEST2’
— SELECT to check newly inserted values       
SELECT  *
FROM    HumanResources.Department
When you resume implicit insertion in identity column, next value for identity column will be the value next to maximum existing value. In our case it will be 56 as we implicitly inserted 55.
Advertisements

Posted on December 14, 2010, in TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

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: