• Register
0 votes
18 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 317 views
317 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 30 views
30 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
0 votes
1 answer 53 views
53 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 4 views
4 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
0 votes
1 answer 64 views
64 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 188 views
188 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, 2020 jwilliam 3.9k points
0 votes
1 answer 106 views
106 views
Problem : I am facing following error with the below query in SQL Server 2012. An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN'. SQL Query: CREATE FUNCTION [dbo].[GetMYPMResources](@UserResourceNo ... JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo WHERE t.TaskManagerNo = @UserResourceNo OR t.AlternateTaskManagerNo = @UserResourceNo ) RETURN END
asked Dec 4, 2019 alecxe 7.5k points
0 votes
1 answer 58 views
58 views
Problem: Hello! I am a new programmer and just started learning C++ about 2 weeks ago. I was working with labels and was trying to run following program when I encountered the above mentioned error: #include<iostream> #include<string> using namespace std; int main() ... what is wrong with my program due to which this error is occurring. If someone knows, kindly help me solving this error. Thanks!
asked Nov 28, 2020 Code Learner 5.3k points
0 votes
1 answer 28 views
28 views
Problem: My window is not starting and I tried to troubleshoot but didn&rsquo;t get my problem solve, can you please tell how can get it solve? A lean operating system that can be used to troubleshoot problems when Windows refuses to start?
asked Feb 19, 2020 maddi86 5.4k points