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

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?

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 :)

If the "archive" table is meant to be an exact copy of you main table then I would just suggest that you remove the fact that the id is an identiy column. That way it will let you insert them.

Alternatively you can allow and the disallow identity inserts for the table with the following statement

SET IDENTITY_INSERT tbl_A_archive ON
--Your inserts here
SET IDENTITY_INSERT tbl_A_archive OFF

Finally, if you need the identity column to work as is then you can always just run the stored proc.

sp_columns tbl_A_archive 

This will return you all of the columns from the table which you can then cut and paste into your query. (This is almost ALWAYS better than using a *)