An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server

sql-server

Solution

Summary

SQL Server won't let you insert an explicit value in an identity column unless you use a column list. Thus, you have the following options:

- Make a column list (either manually or using tools, see below)

OR

- make the identity column in `tbl_A_archive` a regular, non-identity column: If your table is an archive table and you always specify an explicit value for the identity column, why do you even need an identity column? Just use a regular int instead.

Details on Solution 1

Instead of

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table
  SELECT *
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

you need to write

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table (field1, field2, ...)
  SELECT field1, field2, ...
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

with `field1, field2, ...` containing the names of all columns in your tables. If you want to auto-generate that list of columns, have a look at Dave's answer or Andomar's answer.

Details on Solution 2

Unfortunately, it is not possible to just "change the type" of an identity int column to a non-identity int column. Basically, you have the following options:

- If the archive table does not contain data yet, drop the column and add a new one without identity.

OR

- Use SQL Server Management Studio to set the `Identity Specification`/`(Is Identity)` property of the identity column in your archive table to `No`. Behind the scenes, this will create a script to re-create the table and copy existing data, so, to do that, you will also need to unset `Tools`/`Options`/`Designers`/`Table and Database Designers`/`Prevent saving changes that require table re-creation`.

OR

- Use one of the workarounds described in this answer: Remove Identity from a column in a table

Problem

I'm trying to do this query ``` INSERT INTO dbo.tbl_A_archive SELECT * FROM SERVER0031.DB.dbo.tbl_A ``` but even after I ran ``` set identity_insert dbo.tbl_A_archive on ``` I am getting this error message An explicit value for the identity column in table 'dbo.tbl_A_archive' can only be specified when a column list is used and IDENTITY_INSERT is ON. `tbl_A` is a huge table in rows and width, i.e. it has a LOT of columns. I do not want to have to type all the columns out manually. How can I get this to work?

Original source

Related problems