• Register
0 votes

Problem :

I want to do below query

INSERT INTO dbo.tbl_A_archive
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?

7 5 2
3,870 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?).


You can try below option


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"


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

9 7 4
38,600 points

Related questions

0 votes
1 answer 383 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
0 votes
0 answers 1 view
1 view
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 46.7k points
0 votes
1 answer 3 views
Problem: Please assist me in resolving this issue: With ....... statement names can be directly used. module name is not required
asked Mar 30 tuhin1 46.7k points
0 votes
1 answer 8 views
Problem: cannot insert explicit value for identity column in table when identity_insert is set to off
asked Feb 23 Muneeb Saadii 125k points
0 votes
1 answer 64 views
Problem : I am very new to SQL.I and my colleague have recently joined SQL project now I have a table on my SQL Server and I want to export the structure of the table so that I can send it to my colleague. Which SQL command should one fire to get the required table structure? Please note that I don't have any access to the SQL Server Management Studio.
asked Jan 27, 2020 jwilliam 3.9k points
0 votes
1 answer 7 views
Problem: what columns are good index candidates? Specifically for an MS SQL database?
asked Mar 9 ummesalma 22k points
0 votes
1 answer 69 views
Problem: Can anyone guide me with the right solution? If you define a column with a default value, that value is used whenever a row? a. with a zero value for that column is added to the table b. is added to the table c. in the table is updated d. that doesn't include a value for that column is added to the table
asked Feb 17, 2020 maddi86 5.4k points
1 vote
1 answer 2 views
Problem: Need help with the solution of this» You can use the existing value in a column and a calculation to update a value.
asked Mar 29 PkGuy 13.1k points
0 votes
1 answer 4 views
Problem: Is it possible to inject sql to delete the statement? Help.
asked Mar 9 ummesalma 22k points
0 votes
1 answer 20 views
The login is from an untrusted domain and cannot be used with Windows authentication. Whenever I try to connect to a SQL server database, I get this error. Can you give me some advice?
asked Dec 1, 2020 miki 1.9k points