-3

The following script failed to execute

SET IDENTITY_INSERT dbo.MyTable ON

-- the table only has three columns, so there's no ambiguity
INSERT INTO dbo.MyTable
VALUES (1, 2, 3)

SET IDENTITY_INSERT OFF

Which throws the following warning:

An explicit value for the identity column in table 'dbo.MyTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

The script executes as expected when columns are explicit.

SET IDENTITY_INSERT dbo.MyTable ON

INSERT INTO dbo.MyTable (ColA, ColB, ColC)
VALUES (1, 2, 3)

SET IDENTITY_INSERT OFF

Why must a column list be used?

8protons
  • 3,591
  • 5
  • 32
  • 67
  • You have to make a column list for your INSERT statement, not like "INSERT Into tableA Values () – CR241 May 09 '19 at 22:00
  • 1
    You asked "why" then accepted as duplicate a question that doesn't cover that so I've no idea what your intended question was – Martin Smith May 09 '19 at 22:05
  • Asking why Microsoft makes a product work a certain way doesn't always result in a meaningful answer anyway :) – Dale K May 09 '19 at 22:10
  • Just speculation, but. Consider a table like `create table t(id int identity, a int, b int default 3)` and a (legal) statement like `insert into t values (1,2)`. Should the column mapping change if IDENTITY_INSERT is ON? – David Browne - Microsoft May 09 '19 at 23:16

1 Answers1

1

Security. This way it ensures that you do specify the id column otherwise if, by mistake, you have IDENTITY_INSERT ON you can mess up your tables big time.

Carlos Alves Jorge
  • 1,919
  • 1
  • 13
  • 29
  • I don't really buy this explanation. How do you accidentally have `IDENTITY_INSERT` on for a table in a session and specify 1 additional column than would ordinarily be accepted for an insert with a column list? Why wouldn't `DROP DATABASE` have arbitrary hurdles to clear as that is a much more dangerous command? – Martin Smith May 09 '19 at 21:55
  • If I'm not wrong, it's to stop you from entering a duplicate value in that column.. normally, when whatever application writes to this table, the identity of the column (value) will be auto incremented.. possibly because this column is a primary key or part of a primary key or the values in this column need to be unique.. Since you can easily break this with manual entry, you are forced to set the Identity_Insert ON. – Harry May 09 '19 at 22:01
  • @Harry yes that is the explanation for having to `SET IDENTITY_INSERT` - but the question is why having done so the grammar then specifies [`column_list` must be used`](https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017) – Martin Smith May 09 '19 at 22:03
  • 1
    Please remember that Identity column does not have to be the first column of a table. Ensuring the user specifies column list is a good practice regardless of IDENTITY_INSERT being on or off but specially in a case when you are messing with the integrity of the database makes sense to make it mandatory – Carlos Alves Jorge May 09 '19 at 22:30