+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: 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 (/Thread-An-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a-column-list-is-used-and-IDENTITY-INSERT-)
Pages:
1
2
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 - spare543197 - 07-31-2023
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?
RE: 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 - recount683 - 07-31-2023
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 *)
RE: 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 - substantiation474003 - 07-31-2023
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 :)
RE: 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 - cajeput288 - 07-31-2023
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);
RE: 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 - drawknife167783 - 07-31-2023
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
RE: 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 - indomitability471 - 07-31-2023
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.
RE: 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 - francengkdfl - 07-31-2023
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)
RE: 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 - photoreductions615358 - 07-31-2023
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
RE: 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 - expiratory505794 - 07-31-2023
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);
RE: 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 - Progoofing630 - 07-31-2023
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,....)