Sql-server – the rationale for requiring a column list when inserting into a table with identity column

identitysql server

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?

Best 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.

Related Solutions

SQL Server – How to Copy a Table with SELECT INTO Ignoring IDENTITY Property

From Books Online

The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.

Down the page:

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

The SELECT statement contains a join, GROUP BY clause, or aggregate function.

Multiple SELECT statements are joined by using UNION.

The identity column is listed more than one time in the select list.

The identity column is part of an expression.

The identity column is from a remote data source.

If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.

So... you could theoretically get away with:

select id, val 
into copy_from_with_id_2 
from with_id

union all

select 0, 'test_row' 
where 1 = 0;

It would be important to comment this code to explain it, lest it be removed the next time someone looks at it.

Sql-server – IDENTITY_INSERT seems stuck

Is it possible you have two tables with the same name in different schemas? Some random doc I found online states that message 8107 appears when you already have IDENTITY_INSERT set to ON for another table in the db...

Another debugging step to check: You state that the issue keep occurring even after you drop and recreate the table, but what if you drop and don't recreate the table, and try to enable IDENTITY_INSERT on another table? What happens then?

Best Answer

Related Solutions

SQL Server – How to Copy a Table with SELECT INTO Ignoring IDENTITY Property

Sql-server – IDENTITY_INSERT seems stuck

Related Question