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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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      Some of the column names in source table were modified  though values were correct 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   Make a column list  either manually or using tools  see below   OR  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  quot change the type quot  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

[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

Examples related to sql-server