• Register
0 votes
113 views

Problem :

I want to do below query

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

However even after I ran below code

set identity_insert dbo.tbl_A_archive on

I am facing below error message

“Can only be specified when a column list is used and IDENTITY_INSERT is ON.”

tbl_A is the huge table in rows and in width, i.e. it has a huge number of columns. I can not type all of the columns out manually. How can I get this to be fixed?

3.9k points

Please log in or register to answer this question.

1 Answer

0 votes

Solution :

Your error message basically says it all. You either of the below things:

·You can make a column list (a SELECT on the INFORMATION_SCHEMA.COLUMNS and the good text editor)

·You can make the identity column in your tbl_A_archive the regular (non-identity) int column (since it is the archive table, why do you want the identity column?).

OR

You can try below option

SET IDENTITY_INSERT tableA ON

You have to make your column list for the INSERT statement as shown below:

INSERT Into tableA ([id], [c2], [c3], [c4], [c5] ) 
SELECT [id], [c2], [c3], [c4], [c5] FROM tableB

It is not like a "INSERT Into tableA SELECT"

SET IDENTITY_INSERT tableA OFF

I hope above mentioned solutions will help you in fixing your errors.

38.6k points

Related questions

0 votes
1 answer 49 views
49 views
Problem: I came across with this > Can only be specified when a column list is used and identity_insert is on
asked May 2 ummeshani 10.8k points
0 votes
1 answer 475 views
475 views
Problem : I am facing following error while executing script cannot insert explicit value for identity column in table when identity_insert is set to off.
asked Nov 15, 2019 peterlaw 6.9k points
1 vote
1 answer 58 views
58 views
Problem : What does this mean > An explicit value for the identity column in table can only be specified when a column list is used .
asked May 2 Abik Dey 4k points
0 votes
1 answer 18 views
18 views
Problem: cannot insert explicit value for identity column in table when identity_insert is set to off
asked Feb 23 Muneeb Saadii 130k points
0 votes
1 answer 24 views
24 views
Problem: Is anyone able to help me with the following problem: Identity_insert is already on for table
asked Jun 5 nila 121k points
0 votes
1 answer 3 views
3 views
Problem: Set identity_insert on sql server 2008.
asked Jul 24 Priscilla Gurpreet 74.7k points
0 votes
0 answers 24 views
24 views
Problem: As I previously stated in my question, I am having difficulty learning. Could someone help assist me in completing my project?
asked Mar 29 tuhin1 48.9k points
0 votes
1 answer 25 views
25 views
Problem: I have 2 Tables - Employee and Job_History and Both the tables have 3 common columns in it - EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID. Now, I want to Join both the tables using JOIN but not on the basis of all 3 common columns BUT on the basis of only 2 common columns - EMPLOYEE_ID and JOB_ID. How can i get the result ?
asked Apr 26 Supti 13k points
0 votes
1 answer 11 views
11 views
Problem: Is there any way around this : Explicit value must be specified for identity column in table?
asked Jun 21 Nahil 128k points
0 votes
1 answer 35 views
35 views
Problem: I'm unable to identify the complication and need assistance : Only functions and some extended stored procedures can be executed from within a function.
asked Jun 12 asha 121k points