Systems Engineering and RDBMS

Back to the Basics: IDENTITY INSERT

Posted by decipherinfosys on June 4, 2007

We have received e-mails from some of our readers asking for coverage of some fundamentals of SQL, T-SQL, PL/SQL and SQL/PL which are used in day to day life and they had also been kind enough to list out what they would like to see in the those posts. So, on that note, here is one that deals with the explicit inserts into tables that have identity property for one of the fields. As you know, the Identity property is typically used by database designers when they want to use a surrogate key for a table. This allows them to rely on the database engine to automatically increment the value without specifying a value for that Identity property. In the case of Oracle or DB2 LUW, one can use a Sequence and a before trigger to achieve the same effect. DB2 LUW also supports the Identity property so it has the best of both worlds. At times however, there is a need to explicitly insert a value for that Identity property column i.e. there is a need to over-rule the incremental value that is provided by the database engine. One such scenario might be when you are pulling data from one schema to the other and need to get exactly the same ID values across in order to honor the foreign keys in the system. The other can be when you are doing heterogeneous data migrations like migrating data from say Oracle to SQL Server or from Oracle to DB2. Another scenario could be when you are interfacing with another application and need to preserve the ID values sent by that system.

Under such scenarios, one then needs to enable IDENTITY_INSERT for a given table –> this allows explicit values to be inserted into that table. Example:

CREATE TABLE IDENTITY_DEMO
(
IDENTITY_DEMO_ID INT NOT NULL IDENTITY(1,1),
DEMO_TEXT VARCHAR(10) NOT NULL
)
GO

Now, if you try to insert into that table without setting IDENTITY_INSERT ON, you will get this error:

INSERT INTO IDENTITY_DEMO (IDENTITY_DEMO_ID, DEMO_TEXT) VALUES (1, ‘TEST’);

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘IDENTITY_DEMO’ when IDENTITY_INSERT is set to OFF.

In order to be able to do an explicit Identity Insert, you will need to SET the Identity Insert for that table:

SET IDENTITY_INSERT IDENTITY_DEMO ON

INSERT INTO IDENTITY_DEMO (IDENTITY_DEMO_ID, DEMO_TEXT) VALUES (1, ‘TEST’);

(1 row(s) affected)

Once done, you can set the IDENTITY_INSERT to OFF:

SET IDENTITY_INSERT IDENTITY_DEMO OFF

Please remember that at any time, only one table per session can have this property set ON. If you try to do it for another table in the same session, the engine will report that as an error and will list out the table that has that property turned on. Also note that say if the current Identity value for the table is 1000 and the value that you have now inserted is say 2000, this will re-seed the identity value for that table to be 2000. Example:

select ident_current(‘IDENTITY_DEMO’)

—————————————
1000
SET IDENTITY_INSERT IDENTITY_DEMO ON
INSERT INTO IDENTITY_DEMO (IDENTITY_DEMO_ID, DEMO_TEXT) VALUES (2000, ‘TEST’);
SET IDENTITY_INSERT IDENTITY_DEMO OFF
GO

select ident_current(‘IDENTITY_DEMO’)

—————————————
2000

The IDENT_CURRENT() function shown above can be used for getting the current set Identity value for that table. Other such functions that you might find useful are: IDENT_INCR() (to get the increment value – in our example it is set to 1 in the definition itself), IDENT_SEED() (to get the seed value). Per table, you can have only one identity column so these functions take n just the name of the table as the argument and give out the value as per their definitions as mentioned above.

If there are other topics that you would like covered on this blog, please send an e-mail to: info@decipherinfosys.com and we will include those topics in our future blog posts. Also, if you get some time, please provide us with a feedback on the current posts.

2 Responses to “Back to the Basics: IDENTITY INSERT”

  1. […] by decipherinfosys on June 9th, 2007 A couple of days ago, we had posted about the “IDENTITY_INSERT” option in SQL Server in our “Back to the Basics” series. In this post, we will […]

  2. […] of the “SET IDENTITY_INSERT <table_name> ON” command that we have also covered before. Here is how we can do this in the case of a standalone […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: