2

My code:

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

The error:

Msg 8101, Level 16, State 1, Line 4

An explicit value for the identity column in table 'SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn' can only be specified when a column list is used and IDENTITY_INSERT is ON.

What gives?

closed as off-topic by LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace May 5 at 12:15

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.

7

Add a column list to your code:

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn 
    (ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable)
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

GO

Basically, a column list is when you explicitly state what columns you're inserting into.

Just remember that when you set IDENTITY_INSERT ON, you must specify in your insert statement the value for the column that is the identity (in your case the column ColumnWithIdentity). If IDENTITY_INSERT OFF, SQL Server sets the first free value for the identity column, and so you must not add the ColumnWithIdentity field in the SELECT part of your INSERT.

Not the answer you're looking for? Browse other questions tagged or ask your own question.