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

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
RstaibAsked:
Who is Participating?
 
Ken SelviaRetiredCommented:
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
 
Ken SelviaRetiredCommented:
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
 
sukumar_diyaCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sukumar_diyaCommented:
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
 
etmendzCommented:
The suggestion to list the columns names in your INSERT command is correct.
0
 
RstaibAuthor Commented:
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
 
Ken SelviaRetiredCommented:
Thanks for the points. I added a note to that other question indicating column names must be specified.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.