Well, this week I was routinely contributing to the massive indentations in my forehead (banging my head against my keyboard) due to an old SQL Server favourite.. “An explicit value for the identity column in table ‘[Table]’ can only be specified when a column list is used and IDENTITY_INSERT is ON”
Basically, to facilitate inserts (mirroring data) I had to switch off the use of an identity column on the target table.
To do this you normally use the SET IDENTITY_INSERT <Table> ON statement.
The scenario? In order to support the use of changeable (rather than hardcoded) server and schema names, we needed to prime dynamic T-SQL statements and execute them. The problem? An EXEC() statement operates in a separate environment.
In other words, executing a
SET IDENTITY_INSERT [‘ + @DB + ‘].[‘ + @Schema + ‘].[<Table>] ON;
statement is only going to be effective if it is used within the same command batch (the same statement executed by the exec statement) – in other words, it should precede the required insert operation within the same statement, and then executed.
SET @statement =
‘SET IDENTITY_INSERT [‘ + @MirrorDB + ‘].[‘ + @MirrorSchema + ‘].[<Table>] ON;
INSERT INTO [‘ + @MirrorDB + ‘].[‘ + @MirrorSchema + ‘].[<Table>]
…(snip)’
exec ( @statement )
The problem I was encountering was actually unrelated to the error message! After batching the SET statement, it turned out I was using an INSERT INTO statement between two databases (Fully Qualified) – the schemas were the same (the tables both had identity columns and same columns etc), but for some reason the column *ordering* was not the same. Thus, fully addressing the columns works fine:
INSERT [TargetDB].[Schema].[<Table>]
( <columns> )
SELECT
( <columns> )
FROM [SourceDB].[Schema].[<Table>]
WHERE ( <criteria> )
However, rather than returning an error indicating a column mismatch (when using INSERT INTO), we got instead the old familiar “An explicit value for the identity column in table ‘[<Table>]’ can only be specified when a column list is used and IDENTITY_INSERT is ON” – despite the SET statement!
Anyhow, I consider this a lesson learned. Perhaps it will help someone else out there…
