Can only be specified when a column list is used and identity_insert is on.

Question

Problem :

I want to do below query

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

However even after I ran below code

set identity_insert dbo.tbl_A_archive on

I am facing below error message

“Can only be specified when a column list is used and IDENTITY_INSERT is ON.”

tbl_A is the huge table in rows and in width, i.e. it has a huge number of columns. I can not type all of the columns out manually. How can I get this to be fixed?

Answer 1

Your error message basically says it all. You either of the below things:

·You can make a column list (a SELECT on the INFORMATION_SCHEMA.COLUMNS and the good text editor)

·You can make the identity column in your tbl_A_archive the regular (non-identity) int column (since it is the archive table, why do you want the identity column?).

OR

You can try below option

SET IDENTITY_INSERT tableA ON

You have to make your column list for the INSERT statement as shown below:

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

SELECT [id], [c2], [c3], [c4], [c5] FROM tableB

It is not like a "INSERT Into tableA SELECT"

SET IDENTITY_INSERT tableA OFF

I hope above mentioned solutions will help you in fixing your errors.