• 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

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 242 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
1 answer 25 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 maddi86 5.4k points
0 votes
1 answer 45 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 jwilliam 3.9k points
0 votes
1 answer 53 views
Problem : I am trying to import mySql database which was originally an access database into phpmyadmin and its giving me following error: incorrect table definition; there can be only one auto column and it must be defined as a key
asked Nov 14, 2019 peterlaw 6.9k points
0 votes
2 answers 132 views
Problem : I know that my query below causes the error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified. SELECT DISTINCT mycity FROM Employees WHERE mycountry = N'USA' AND myregion = N'WA' ORDER BY mybirthdate; What is a reason for ... gives me some output, but not the error. Is it because SELECT DISTINCT mycity only gives the result set with cities and nothing else ?
asked Jan 17 jwilliam 3.9k points