Solved

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

Posted on 2004-10-24
413 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
Question by:Rstaib
    7 Comments
     
    LVL 12

    Accepted Solution

    by:
    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
    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
    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
     
    LVL 7

    Expert Comment

    by:sukumar_diya
    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
    The suggestion to list the columns names in your INSERT command is correct.
    0
     

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    The steps for moving the system databases to a new location are documented in the following technical article: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx. However sometimes after the moving process is finished, though SQL i…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    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.

    856 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now