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?
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:
- Make a column list (either manually or using tools, see below)
- make the identity column in
tbl_A_archivea 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
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;
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.
- Use SQL Server Management Studio to set the
(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
Table and Database Designers/
Prevent saving changes that require table re-creation.
- Use one of the workarounds described in this answer: Remove Identity from a column in a table
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
Read more... Read less...
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.
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 *)
For the SQL statement, you also have to specify the column list. For eg.
INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)
INSERT INTO tbl VALUES ( value1,value2)
Both will work but if you still get error by using #1 then go for #2
SET IDENTITY_INSERT customers ON GO insert into dbo.tbl_A_archive(id, ...) SELECT Id, ... FROM SERVER0031.DB.dbo.tbl_A
SET IDENTITY_INSERT customers ON GO insert into dbo.tbl_A_archive(id, ...) VALUES(@Id,....)