In this article we will learn about IDENTITY_INSERT in sql and how we can insert rows with primary key identity column.
For inserting explicit values into the identity column of a table we use IDENTITY_INSERT. We can set its property ON or OFF.
Syntax:
SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }
database_name: Name of database having table.
schema_name: Name of the schema having table.
table_name: Name of a table with an identity column in which we want to insert values explicitly.
Note: With in current user session, only one table can have IDENTITY_INSERT property is ON.
If a table already has this property set to ON, and we SET IDENTITY_INSERT ON statement for another table, SQL Server returns an error message.
Example:
Lets create an table with identity column dbo.Country
CREATE table dbo.Country(CountryId int identity Primary Key, CountryName varchar(200))
Go
Whenever we will insert values in dbo.Country table CountryId is generated internally as shown bellow.
By Default IDENTITY_INSERT is off when table is created with identity.
Lets insert few values.
INSERT INTO dbo.Country Values('Australia'),('Russia'),('China')
Go
SELECT * FROM dbo.Country
Go
OUTPUT: CountryId CountryName ----------- ------------- 1 Australia 2 Russia 3 China (3 rows affected)
The CountryId 1,2,3 generated internally.
What will happens if we will insert data with CountryId ?
INSERT INTO dbo.Country(CountryId,CountryName) Values(4,'UK')
Go
OUTPUT:
Msg 544, Level 16, State 1, Line 4
Cannot insert explicit value for identity column in table 'Country' when IDENTITY_INSERT is set to OFF.
Qus: So how can we insert identity column value manually ?
Ans: IDENTITY_INSERT
Set IDENTITY_INSERT for given table property to ON.
SET IDENTITY_INSERT dbo.Country ON
Go
Insert Date into Country table with identity column CountryId
INSERT INTO dbo.Country(CountryId,CountryName) Values(4,'UK')
Go
SELECT * FROM dbo.Country
Go
OUTPUT: CountryId CountryName ----------- ------------ 1 Australia 2 Russia 3 China 4 UK (4 rows affected)
After inserting data manually turn OFF IDENTITY_INSERT.
SET IDENTITY_INSERT dbo.Country OFF
Go
So Use three statements back to back as follows.
SET IDENTITY_INSERT dbo.Country ON
Go
INSERT INTO dbo.Country(CountryName) Values('UK')
Go
SET IDENTITY_INSERT dbo.Country OFF
Go
if you forget to turn off IDENTITY_INSERT for current session and insert value into table without identity column sql server will throw an exception for it.
INSERT INTO dbo.Country(CountryName) Values('Canada')
Go
OUTPUT:
Msg 545, Level 16, State 1, Line 5
Explicit value must be specified for identity column in table 'Country' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
Note: Never forget to turn off IDENTITY_INSERT property after use.
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.