[sql-server] 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?

For if you want to insert your values from one table to another through a stored procedure. I used this and this, the latter which is almost like Andomar's answer.

CREATE procedure [dbo].[RealTableMergeFromTemp]
    with execute as owner
AS
BEGIN
BEGIN TRANSACTION RealTableDataMerge
SET XACT_ABORT ON

    DECLARE @columnNameList nvarchar(MAX) =
     STUFF((select ',' + a.name
      from sys.all_columns a
      join sys.tables t on a.object_id = t.object_id 
       where t.object_id = object_id('[dbo].[RealTable]') 
    order by a.column_id
    for xml path ('')
    ),1,1,'')

    DECLARE @SQLCMD nvarchar(MAX) =N'INSERT INTO [dbo].[RealTable] (' + @columnNameList + N') SELECT * FROM [#Temp]'

    SET IDENTITY_INSERT [dbo].[RealTable] ON;
    exec(@sqlcmd)
    SET IDENTITY_INSERT [dbo].[RealTable] OFF

COMMIT TRANSACTION RealTableDataMerge
END

GO

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

There is one or more column that has auto-increment property or the value of that attribute will be calculated as constraints. You are trying to modify that column.

There is two way to solve it 1) Mention other columns explicitly and set their values only and the PrimaryKey or the auto-increment column value will set automatically.

2) You can turn on INDENTITY_INSERT then execute your insert query finally turn off IDENTITY_INSERT.

Suggestion: Follow the first step because it is a more suitable and efficient approach.

For more information read this article on SQL-helper.

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

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,....)

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 use the following to create a temp exact as the table but without the identity:

SELECT TOP 0 CONVERT(INT,0)myid,* INTO #temp FROM originaltable

ALTER TABLE #temp DROP COLUMN id

EXEC tempdb.sys.sp_rename N'#temp.myid', N'id', N'COLUMN'

Gets a warning about renames but no big deal. I use this on production class systems. Helps make sure the copy will follow any future table modifications and the temp produced is capable of getting rows additional times within a task. Please note that the PK constraint is also removed - if you need it you can add it at the end.

Please make sure that the column names, data types, and order in the table from where you are selecting records is exactly same as the destination table. Only difference should be that destination table has an identity column as the first column, that is not there in source table.

I was facing similar issue when I was executing "INSERT INTO table_Dest SELECT * FROM table_source_linked_server_excel". The tables had 115 columns.

I had two such tables where I was loading data from Excel (As linked server) into tables in database. In database tables, I had added an identity column called 'id' that was not there in source Excel. For one table the query was running successfully and in another I got the error "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". This was puzzling as the scenario was exactly same for both the queries. So I investigated into this and what I found was that in the query where I was getting error with INSERT INTO .. SELECT *:

1. Some of the column names in source table were modified, though values were correct
2. There were some extra columns beyond actual data columns that were being selected by SELECT *. I discovered this by using the option of "Script table as > Select to > new query window" on the source Excel table (under linked servers). There was one hidden column just after the last column in Excel, though it did not have any data. I deleted that column in source Excel table and saved it.

After making the above two changes the query for INSERT INTO... SELECT * ran successfully. The identity column in destination table generated identity values for each inserted row as expected.

So, even though the destination table may have an identity column that is not there in source table, the INSERT INTO.. SELECT * will run successfully if the names, data types, and column order in source and destination are exactly the same.

Hope it helps someone.

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

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

In order to populate all of the column names into a comma-delimited list for a Select statement for the solutions mentioned for this question, I use the following options as they are a little less verbose than most responses here. Although, most responses here are still perfectly acceptable, however.

1)

SELECT column_name + ',' 
FROM   information_schema.columns 
WHERE  table_name = 'YourTable'

2) This is probably the simplest approach to creating columns, if you have SQL Server SSMS.

1) Go to the table in Object Explorer and click on the + to the left of the table name or double-click the table name to open the sub list.

2) Drag the column subfolder over to the main query area and it will autopaste the entire column list for you.

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

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.

SET IDENTITY_INSERT tableA ON

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

Not like this

INSERT INTO tableA
SELECT * FROM tableB

SET IDENTITY_INSERT tableA OFF

This code snippet shows how to insert into table when identity Primary Key column is ON.

SET IDENTITY_INSERT [dbo].[Roles] ON
GO
insert into Roles (Id,Name) values(1,'Admin')
GO
insert into Roles (Id,Name) values(2,'User')
GO
SET IDENTITY_INSERT [dbo].[Roles] OFF
GO

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