[Webinar] Streamline your web hosting managementRegister Today

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
?
434 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
7 Comments
 
LVL 12

Accepted Solution

by:
Ken Selvia 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:Ken Selvia
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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:Ken Selvia
ID: 12406405
Thanks for the points. I added a note to that other question indicating column names must be specified.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

591 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