Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list? [closed]

Question

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?

Answer 1

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.