[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to copy a sql table2 to sql table 2 and preserve the key identity

Posted on 2004-10-24
7
Medium Priority
?
429 Views
Last Modified: 2008-03-10
I know this question has been asked and answered several times. (I have read them all) but it is not working for me.

I want to trransfer a table from a development server to a production server.

On the production server I have two tables "products" and and exact twin called XFRproducts. Products has the key catalogid set to indetity(1,1) and XFRproducts has catalogid as an integer but no identity.

I have an enterprise manager package that truncates xfrproducts on the production server and then copies products on the development server to xfrproducts on the production server.  This is a large table and takes quite a while. Meanwhile, applications that use the products table on the production server continue to run perfectly. All of this works fine, then I want to execute a script to copy the rows from the XFRproducts table to the products table and preserve the catalogid identity.

(many other fields in the database use the catalogid value to point between records).

here is the code and the result.

first, I used the following code in Query Analyzer, which is very close to the accepted answer to someone else's question about a year ago:

USE dbname
truncate table products
SET IDENTITY_INSERT products ON
Insert Into products select  *  FROM XFRproducts
Set IDENTITY_INSERT products OFF

and got,

Server: Msg 8101, Level 16, State 1, Line 4
An explicit value for the identity column in table 'products' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I assumed that the error message was telling me to name every column since I already had set IDENTITY_INSERT ON, so I changed it as follows:


USE dbname
truncate table products
SET IDENTITY_INSERT products ON
Insert Into products select  catalogid, b,c,d,e,f,g...a long list of 74 columns)  FROM XFRproducts
Set IDENTITY_INSERT products OFF

again I got the exact same error message
Server: Msg 8101, Level 16, State 1, Line 4
An explicit value for the identity column in table 'products' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I have checked for typos, etc until I am crosseyed.  Is the some basic error?  What am I doing wrong.

Thanks
Bob
0
Comment
Question by:Rstaib
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 12

Accepted Solution

by:
kselvia earned 2000 total points
ID: 12397518
Afraid you will have to list the 74 columns of product too;

Insert Into products (col1, col2, col3,...)
   select  catalogid, b,c,d,e,f,g...a long list of 74 columns)  FROM XFRproducts
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12397522
BTW: That is what the error says.

  An explicit value for the identity column in table 'products' can only be specified >when a column list is used< and IDENTITY_INSERT is ON.

You were focusing on the wrong part of the problem :)
0
 
LVL 7

Expert Comment

by:sukumar_diya
ID: 12397681
hi pal,
include the column list after the product and check like the the below one

USE dbname
truncate table products
SET IDENTITY_INSERT products ON
Insert Into products
(catalogid, b,c,d,e,f,g...a long list of 74 columns)  -- include this and test
select  catalogid, b,c,d,e,f,g...a long list of 74 columns)  FROM XFRproducts
Set IDENTITY_INSERT products OFF

Hope this helps you,
Sukumar

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 7

Expert Comment

by:sukumar_diya
ID: 12397691
when i used the following the same error thing happend to me....

truncate table yrweight
GO
SET IDENTITY_INSERT yrweight ON
GO
insert into yrweight select id,weight,yrweight,cost from  yrweight1
GO
Set IDENTITY_INSERT yrweight OFF
Go


I modified like this and works fine..

truncate table yrweight
GO
SET IDENTITY_INSERT yrweight ON
GO
insert into yrweight(id,weight,yrweight,cost)  select id,weight,yrweight,cost from  yrweight1
GO
Set IDENTITY_INSERT yrweight OFF
Go



Cheers
Sukumar
0
 
LVL 6

Expert Comment

by:etmendz
ID: 12397709
The suggestion to list the columns names in your INSERT command is correct.
0
 

Author Comment

by:Rstaib
ID: 12406237
Oh to be so close and yet so far away.   I am acception the answer from kselvia because it was first.
Sukumar had essentially the same answer, right also.

Moderator please note that there is a similar question, with an accepted answer that is not really correct.  The link below implies that you can use the select * which is not correct.  can you add a link from those answers to this answer to help the poor next guy (or gal) who gets hung up on this simple problem.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20749662.html?query=copy+sql+tables+and+preserve+identity&clearTAFilter=true

thanks for the help
bob
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12406405
Thanks for the points. I added a note to that other question indicating column names must be specified.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question