Posted By: Anonymous
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