1

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.

  • Isn't this the column list (or am I being stupid? lol): SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable – J.D. May 3 at 16:16
6

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.

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