• Register
0 votes
45 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?

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?).

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.

9 7 4
38,600 points

Related questions

0 votes
1 answer 383 views
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
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
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
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
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
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
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
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
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