Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
+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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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