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

I'm trying to do this query

INSERT INTO     dbo.tbl_A_archive
SELECT  *
FROM        SERVER0031.DB.dbo.tbl_A

but am getting the following error message even after i do

set identity_insert dbo.tbl_A_archive on

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? Please help!

Answer 1

Well, the error message basically says it all. You have the following options:

• Make a column list (a SELECT on INFORMATION_SCHEMA.COLUMNS and a good text editor or the solutions proposed by Andomar and Dave can help you with this)

OR

• make the identity column in tbl_A_archive a regular (non-identity) int column (since it's an archive table, why do you need an identity column?).

Answer 2

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

Answer 3

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.

Answer 4

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