Sql Server INSERT data into an IDENTITY column with SET IDENTITY_INSERT ON
- Posted by Sqltimes
- On November 15, 2014
- 0 Comments
Quick one today:
Rare, but sometimes, there is a need to INSERT specific values into a table that has an IDENTITY specified on one of the columns. Luckily, Sql Server provides mechanisms to insert data in such situations.
When we try to insert values into an IDENTITY column, we get errors like this.
Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'Product' when IDENTITY_INSERT is set to OFF.
|
1
2
3
4
5
6
7
8
9
|
---- Using IDENTITY_INSERT ON--SET IDENTITY_INSERT dbo.Products ONGOINSERT INTO dbo.Products (ProductID, ProductTypeID, Description, LastUpdate)VALUES (1232, 345, 'tooth Paste', GETDATE())GO |
Now, after done inserting records, reset the IDENTITY_INSERT flag using SET statement.
|
1
2
3
4
5
|
---- Reset the IDENTITY_INSERT flag--SET IDENTITY_INSERT dbo.Products OFFGO |
Hope this helps,
0 Comments