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

Question

SET IDENTITY_INSERT tableA ON

You have to make a column list for your INSERT statement:

INSERT Into tableA ([id], [c2], [c3], [c4], [c5] ) 
SELECT [id], [c2], [c3], [c4], [c5] FROM tableB

not like "INSERT Into tableA SELECT ........"

SET IDENTITY_INSERT tableA OFF

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:

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

OR

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

If you're using SQL Server Management Studio, you don't have to type the column list yourself - just right-click the table in Object Explorer and choose Script Table as -> SELECT to -> New Query Editor Window.

If you aren't, then a query similar to this should help as a starting point:

SELECT SUBSTRING(
    (SELECT ', ' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'tbl_A'
        ORDER BY ORDINAL_POSITION
        FOR XML path('')),
    3,
    200000);

Agree with Heinzi's answer. For first second option, here's a query that generates a comma-separated list of columns in a table:

select name + ', ' as [text()] 
from sys.columns 
where object_id = object_id('YourTable') 
for xml path('')

For big tables, this can save a lot of typing work :)