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 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?

Answer 1

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