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

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 242 views
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
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
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
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
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