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?

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.

| improve this answer | |

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