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

Summary

SQL Server won't let you insert an explicit value in an identity column unless you use a column list. Thus, you have the following options:

1. Make a column list (either manually or using tools, see below)

OR

2. make the identity column in tbl_A_archive a regular, non-identity column: If your table is an archive table and you always specify an explicit value for the identity column, why do you even need an identity column? Just use a regular int instead.

Details on Solution 1

Instead of

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table
  SELECT *
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

you need to write

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table (field1, field2, ...)
  SELECT field1, field2, ...
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

with field1, field2, ... containing the names of all columns in your tables. If you want to auto-generate that list of columns, have a look at Dave's answer or Andomar's answer.

Details on Solution 2

Unfortunately, it is not possible to just "change the type" of an identity int column to a non-identity int column. Basically, you have the following options:

• If the archive table does not contain data yet, drop the column and add a new one without identity.

OR

• Use SQL Server Management Studio to set the Identity Specification/(Is Identity) property of the identity column in your archive table to No. Behind the scenes, this will create a script to re-create the table and copy existing data, so, to do that, you will also need to unset Tools/Options/Designers/Table and Database Designers/Prevent saving changes that require table re-creation.

OR

• Use one of the workarounds described in this answer: Remove Identity from a column in a table

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

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

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

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

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

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.

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.

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.

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