Setting ON/OFF IDENTITY_INSERT in SqlServer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Setting ON/OFF IDENTITY_INSERT in SqlServer

Author

Topic

uttam548
Starting Member

12 Posts

Posted - 2011-07-18 : 10:55:51

I need to re-insert few records in a table which have been deleted previously. One of my column is an IDENTITY column (there is no primary key in this table), so I was thinking of
1. SET IDENTITY_INSERT ON on that column
2. INSERT the records
3. SET IDENTITY_INSERT OFF
But I am not sure how this will affect my other operations like INSERT AND UPDATEs. I need to do this on a live database. I am looking forward to your ideas/suggestions on this matter. Thanks in advance.
uttam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-18 : 11:19:48

Shouldn't affect them at all, as long as no one reseeded the table.

First check to be sure. DBCC CHECKIDENT(yourTable)

If the number is lower than any of the records you're about to insert, reseed it to something high enough to stay out of your way.

uttam548
Starting Member

12 Posts

Posted - 2011-07-18 : 12:34:12

Thanks russel for your reply.
I have my table like this.
CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(1,1) NOT NULL,
[col1] [varchar](50) NULL
)
INSERT INTO test values('AAA')
INSERT INTO test values('BBB')
INSERT INTO test values('CCC')
INSERT INTO test values('DDD')
DELETE FROM test WHERE col1 = 'CCC' (this row had id = 3)
Now i try to re-insert this record and execute
SET IDENTITY_INSERT test ON.
What will happen when someone tries to insert a record into my table (INSERT INTO test values ('EEE')) before my insert statement (INSERT INTO test (id,col1) VALUES (3,'CCC')) gets executed? I think it won't give a new id, since IDENTITY_INSERT is set ON at the moment. What will happen to that first INSERT statement??

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-18 : 14:10:23

No, theirs will have an id of 5.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-07-19 : 05:27:29

I would suspect that the other insert will fail...."IDENTITY(1,1) NOT NULL"...as the regular "insert" won't be supplying an ID value.

During this operation you would need to suspend all other "insert" activity to this table.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-19 : 09:17:48

No, SET IDENTITY_INSERT <table> ON is a session level setting, not a global one, so the other insert would work as expected.