Insert values into identity column in sql server

Introduction:

In my previous article I have explained about Use of SET NOCOUNT in sql server. In this article I am going to explain about how to insert values into identity column or the use of  SET IDENTITY_INSERT in sql server.

Explanation:

SET IDENTITY_INSERT Allows explicit values to be inserted into the identity column of a table.

Syntax: 

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }       

Arguments:

database_name - Is the name of the database in which the specified table resides.

schema_name - Is the name of the schema to which the table belongs.

Table -Is the name of a table with an identity column.

At any time, only one table in a session can have the  IDENTITY_INSERT  property set to  ON . If a table already has this property set to ON  , and a  SET IDENTITY_INSERT ON  statement is issued for another table, SQL Server returns an error message that states  SET  IDENTITY_INSERT  is already ON and reports the table it is set  ON  for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of  SET IDENTITY_INSERT  is set at execute or run time and not at parse time.

Example:

The following example creates a table with an identity column and shows how the SET IDENTITY_INSERT  setting can be used to fill a gap in the identity values caused by a DELETE statement.

USE DotNetPickles;

GO

-- Create tool table.

CREATE TABLE dbo.Tool(

   ID INT IDENTITY NOT NULL PRIMARY KEY,

   Name VARCHAR(40) NOT NULL

)

GO

-- Inserting values into products table.

INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')

INSERT INTO dbo.Tool(Name) VALUES ('Hammer')

INSERT INTO dbo.Tool(Name) VALUES ('Saw')

INSERT INTO dbo.Tool(Name) VALUES ('Shovel')

GO

-- Create a gap in the identity values.

DELETE dbo.Tool WHERE Name = 'Saw'

GO

SELECT * FROM dbo.Tool

GO

-- Try to insert an explicit ID value of 3;

-- should return a warning.

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')

GO

-- SET IDENTITY_INSERT to ON.

SET IDENTITY_INSERT dbo.Tool ON

GO

-- Try to insert an explicit ID value of 3.

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')

GO

SELECT *

FROM dbo.Tool

GO

-- Drop products table.

DROP TABLE dbo.Tool

GO

Do you like this article. Then share with your friends. Or like our Facebook page. Or post your comments below…