Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

SQL Server - How do I Insert an explicit value into an IDENTITY column? How do I Update the value of an IDENTITY Column?

Jan 30 2009 12:10AM by Jacob Sebastian   

IDENTITY columns are managed by SQL Server. Most of the times you would not dare to touch the values, except for reading them. However, there may be times when you really need to update/insert explicit values, instead of letting SQL Server generate a value for you.

How do I insert an explicit value into an IDENTITY column?

By default SQL Server does not allow you to insert a value into an IDENTITY column. You need to explicitly enable IDENTITY INSERT. Let us see some sample code to understand this. Run the following script to create the sample table and data.

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
DELETE FROM Employees WHERE Name = 'Steve'
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
3           Bob
*/

Note that we inserted two records, then deleted the record of 'Steve' and then inserted 'Bob'. This indeed created a missing sequence number. The next insert will generate IDENTITY value 4. Let us try to insert a record for Steve with EmpID 2.

SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 2, 'Steve'
SET IDENTITY_INSERT Employees OFF
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
3           Bob
*/

SET IDENTITY_INSERT ON enables IDENTITY INSERT on the specified table. You should TURN it off as soon as you are done with inserting the value. Only one TABLE per session can have IDENTITY INSERT set to ON at a time. If you try to enable IDENTITY INSERT on another table, SQL Server will generate an error.

SET IDENTITY_INSERT Employees ON
CREATE TABLE Departments (DeptID INT IDENTITY)
GO
SET IDENTITY_INSERT Departments ON
/*
Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'master.dbo.Employees'. 
Cannot perform SET operation for table 'Departments'.
*/

To enable IDENTITY INSERT on another table, you should set it to OFF on the previous table. The following is valid.

SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 5, 'Jacob'
SET IDENTITY_INSERT Employees OFF
-- enable IDENTITY INSERT on Departments Table
SET IDENTITY_INSERT Departments ON

When IDENTITY INSERT is set to ON, you should always specify an IDENTITY value, else the insert operation will fail.

SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (Name) SELECT 'Steve'
/*
Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table 
'Employees' either when IDENTITY_INSERT is set to ON or when a 
replication user is inserting into a NOT FOR REPLICATION 
identity column.
*/

When you explicitly insert a new IDENTITY value, if that is higher than the current identity value, SQL Server will automatically adjust the current IDENTITY value to the new value.

TRUNCATE TABLE Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
-- current IDENTITY VALUE is 2

SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 100, 'Jacob'
SET IDENTITY_INSERT Employees OFF
-- current IDENTITY VALUE IS 100

INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
100         Jacob
101         Jacob
*/

How do I UPDATE the value of an identity column?

You cannot update an IDENTITY column. SQL Server will generate an error if you attempt to do that. The following example demonstrates that.

SET IDENTITY_INSERT Employees ON
UPDATE Employees SET
    EmpID = EmpID + 100
WHERE Name = 'Jacob'
/*
Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'EmpID'.
*/

Question to Readers: How often do you insert explicit values to an IDENTITY column? Did you ever come across cases where you needed to UPDATE an IDENTITY value?

Tags: IDENTITY, TSQL, SQL SERVER, SET IDENTITY_INSERT, UPDATE IDENTITY, EXPLICIT IDENTITY VALUE,


Jacob Sebastian
1 · 100% · 32235
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

5  Comments  

  • Jacob, I have a case where I insert the same data (just presented differently) into two tables used by two different clients. Up to this point it didn't matter if the ID field (identity) were the same or not. For the new year the two collaborating clients decided they wanted the IDs to be the same. I reseeded both tables using the IDENTITY_INSERT technique. The first record inserted after the reseed incremented fine with both tables having the same ID (4000). On the next insert one table skipped 4001 and recorded 4002 where the other table recorded 4001. Any idea what would cause this?

    commented on Dec 31 2012 11:06AM
    Jim Butchart
    2764 · 0% · 4
  • IDENTITY values are not expected to be sequential. It possible to have gaps between the values. The most common reasons for this are transaction rollbacks. For example

    CHECK IDNETITY => 100
    BEGIN TRAN
        INSERT
    ROLLBACK TRAN
    CHECK IDENTITY => 101
    

    In the above exmaple, the identity seed increases by 1 even though the transaction has been rolled back. Is this the case happening in your case?

    commented on Jan 1 2013 10:46AM
    Jacob Sebastian
    1 · 100% · 32235
  • No, that wouldn't be the case here. I insert into the two tables in the same transaction. If there was a rollback it would effect both. It's still a mystery.

    commented on Jan 1 2013 2:10PM
    Jim Butchart
    2764 · 0% · 4
  • Are you able to reproduce the problem at will? If so do you have a short repro script that I can try on my side?

    commented on Jan 2 2013 6:01AM
    Jacob Sebastian
    1 · 100% · 32235
  • No. I have never hoped for an anomaly but that is what I hope this was and won't happen again...

    commented on Jan 2 2013 11:07AM
    Jim Butchart
    2764 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

"SQL Server - How do I Insert an explicit value into an IDENTITY column? How do I Update the value of an IDENTITY Column?" rated 5 out of 5 by 2 readers
SQL Server - How do I Insert an explicit value into an IDENTITY column? How do I Update the value of an IDENTITY Column? , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]