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?

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

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

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

You must need to specify columns name which you want to insert if there is an Identity column.
So the command will be like this below:

SET IDENTITY_INSERT DuplicateTable ON

INSERT Into DuplicateTable ([IdentityColumn], [Column2], [Column3], [Column4] )
SELECT [IdentityColumn], [Column2], [Column3], [Column4] FROM MainTable

SET IDENTITY_INSERT DuplicateTable OFF

If your table has many columns then get those columns name by using this command.

SELECT column_name + ','
FROM information_schema.columns
WHERE table_name = 'TableName'
for xml path('')

(after removing the last comma(',')) Just copy past columns name.

For the SQL statement, you also have to specify the column list. For eg.

INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)

instead of

INSERT INTO tbl VALUES ( value1,value2)

I think this error occurs due to the mismatch with number of columns in table definition and number of columns in the insert query. Also the length of the column is omitted with the entered value.
So just review the table definition to resolve this issue

This should work. I just ran into your issue:

SET IDENTITY_INSERT dbo.tbl_A_archive ON;
INSERT INTO dbo.tbl_A_archive (IdColumn,OtherColumn1,OtherColumn2,...)
SELECT *
FROM SERVER0031.DB.dbo.tbl_A;
SET IDENTITY_INSERT dbo.tbl_A_archive OFF;

Unfortunately it seems you do need a list of the columns including the identity column to insert records which specify the Identity. **However**, you don't HAVE to list the columns in the SELECT.
As *@Dave Cluderay* suggested this will result in a formatted list for you to copy and paste (if less than 200000 characters).

I added the USE since I'm switching between instances.

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

Both will work but if you still get error by using #1 then go for #2

1)

SET IDENTITY_INSERT customers ON
GO
insert into dbo.tbl_A_archive(id, ...)
SELECT Id, ...
FROM SERVER0031.DB.dbo.tbl_A

2)

SET IDENTITY_INSERT customers ON
GO
insert into dbo.tbl_A_archive(id, ...)
VALUES(@Id,....)