CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user1108948
user1108948

Reputation:

Set IDENTITY_INSERT ON is not working

I want to copy a table Equipment from one database MyDBQA to our test database MyDB. There is an identity column in the table which is the primary key (int, not null).

But I got an error:

Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'MyDB.dbo.Equipment' can only be specified when a column list is used and IDENTITY_INSERT is ON.

My script:

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON
INSERT INTO [MyDB].[dbo].[Equipment]  SELECT * FROM [MyDBQA].[dbo].[Equipment]
SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF

Upvotes: 59

Views: 186196

Answers (4)

user4272270
user4272270

Reputation: 49

In VB code, when trying to submit an INSERT query, you must submit a double query in the same 'executenonquery' like this:

sqlQuery = "SET IDENTITY_INSERT dbo.TheTable ON; INSERT INTO dbo.TheTable (Col1, COl2) VALUES (Val1, Val2); SET IDENTITY_INSERT dbo.TheTable OFF;"

I used a ; separator instead of a GO.

Works for me. Late but efficient!

Upvotes: 4

Michal Klouda
Michal Klouda

Reputation: 14521

You might be just missing the column list, as the message says

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON

INSERT INTO [MyDB].[dbo].[Equipment]
            (COL1,
             COL2)
SELECT COL1,
       COL2
FROM   [MyDBQA].[dbo].[Equipment]

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF

Upvotes: 114

user2574095
user2574095

Reputation: 11

Here's Microsoft's write up on using SET IDENTITY_INSERT, which might be helpful to others seeing this post if they, like me, found this post when trying to recreate deleted records while maintaining the original identity column value.

to recreate deleted records with original identity column value: http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

Upvotes: 1

Heinzi
Heinzi

Reputation: 172200

The relevant part of the error message is

...when a column list is used...

You are not using a column list, you are using SELECT *. Use a column list instead:

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON 

INSERT INTO [MyDB].[dbo].[Equipment] (Col1, Col2, ...)
SELECT Col1, Col2, ... FROM [MyDBQA].[dbo].[Equipment] 

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF

Upvotes: 19

Related Questions