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

For if you want to insert your values from one table to another through a stored procedure. I used [this][1] and [this][2], 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


[1]:

[To see links please register here]

[2]:

[To see links please register here]

```
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.

```sql
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
```

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.

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][1].


[1]:

[To see links please register here]

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.

**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](

[To see links please register here]

) or [Andomar's answer](

[To see links please register here]

).

----

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

[To see links please register here]

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.

> "an explicit value for the identity column in table can only be
> specified when ..."

What fixed it for me, was not specifying the identity column.

So instead of

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

I specified all columns but not the identity one (tbl_A_archive.ID)

INSERT INTO dbo.tbl_A_archive
SELECT col1, col2, colETC
FROM SERVER0031.DB.dbo.tbl_A