Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: How do I move data and preserve foreign key relationships

    How do I move data from one server and append to the same table structure on a second server and preserve the foreign key relationships. In other words TableB.TableA_ID references TableA.ID. The IDENTITY ID numbers themselves will change but they must continue to match up on the destination server the way they did on the source server.

    I can do this using procedural programming but that is usually not the way to go.

    (btw, this is a cleaned up version of a question I tacked on to another thread)

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you can use the copy database wizard but all of the user have to be logged off and I get fustrated with it's quirkiness.

    you can use sp_detach_db on the database in the original server. copy the physical files onto the destination server. use sp_attach_db on the target server and you will need to use sp_attach_db on the orginal server as well.

    Is this a one time move or will you need to do this on a regular basis? These solutions are good for a 1 time move. Other things should be done for a routine deal.

    Of course, read about this stuff before you do it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Review the SET IDENTITY INSERT ON/OFF option. It should allow you to copy in your data without changing the existing identity values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Thrasymachus
    you can use the copy database wizard ... and I get fustrated with it's quirkiness...
    What quirkiness?

    Quote Originally Posted by Thrasymachus
    you can use sp_detach_db ... and ... sp_attach_db ...
    That's what the wizard does, so how can it be option #2???
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The copy database wizard has given me troubles in the past. I forget exactly what the issue was as it was some time ago. It is almost always better to do it from the QA than the Enterprise Mangeler but if you like the wizard use it. Stop trying to start trouble for trouble's sake.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by blindman
    Review the SET IDENTITY INSERT ON/OFF option. It should allow you to copy in your data without changing the existing identity values.
    If the target server is blank or you can completely replace with the source data there will be no ID overlap and that works very well.

    What if you can't blow any the destintation, you need to add the source rows, and the IDs overlap so that they must be changed? For example, what if the target server has data for IDs 1-100 and the source server has data for 10 different rows that use IDs 1-10.

    Using IDENTITY INSERT and manually calculating an ID offset would work although I'm not sure that that would be a safe solution (particularly in regards to race conditions).

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Temporarily add a column to your destination table that stores and preserves the original ID value. Then run an update query to synchronize your child records with the new ID values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by blindman
    Temporarily add a column to your destination table that stores and preserves the original ID value. Then run an update query to synchronize your child records with the new ID values.
    That will work. Good idea.

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •