0

When inserting into a table with an identity column from some similar staging table, we have to set IDENTITY_INSERT to ON and specify a column list.

Why do we have to specify a column list in this case?

Wouldn't it be better to specify a change request at Microsoft Connect?

4

If you don't specify the column list then this implicitly assumes a column list including all user insert-able columns (non identity, rowversion, computed) as below.

CREATE TABLE #T
(
C INT IDENTITY,
D INT,
E INT
)

INSERT INTO #T
VALUES (2,3)

This is far more generally useful as the most common reason for having an IDENTITY column is to allow SQL Server to manage the values.

I suppose it could imply that if there was an extra column in the insert source on an identity table that it should generate an execution plan for the explicit identity_insert case but not much benefit to this IMO.

  • +1 - You can see this behavior in SSMS as well - auto-generated insert scripts don't include the identity field in the field list. – JNK Nov 21 '11 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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