Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: Importing Data With Duplicate Keys

    I'm trying to merge two Access databases into one SQL server database. I have 3 tables that are all related with primary and foreign keys.

    When I try to import my second set of 3 tables I get errors about the keys already existing in the database. Is there any way to force SQL server to assign new keys while preserving my existing relationships? Thanks!
    BillS

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No.
    You will have to update the keys in two or your Access databases to ensure that no records from the three databases conflict. And you will likely also have to deal with issues where you have identical records in each database that should be merged rather than duplicated (such as in lookup lists...).
    Or, another possible solutions is to load the data from separate databases into separate versions of the same SQL Server table, assigning new GUID identifiers simultaneously. The three tables can then be merged using GUIDs rather than the integer/identity values you probable have right now. Note that you will still need to update foreign keys in related tables to reference the new GUIDs.
    These things are all just part of a DBA's day...
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Blah, I knew this wouldn't be fun. Thanks for the info.
    BillS

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    want to show us some DDL and sample data of what the problem is?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I never said it wouldn't be fun. I just said it is what we DBAs do all day.

    Every day.

    Day in, day out...

    Day after day after day after day...

    Hell, it's a blast.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i have 2 contradictory seanisms on this...

    I sometimes tell the bellachers around here

    "if you ain't having fun, go do something else for a living"

    and when they complain sometimes I tell them...

    "if it was easy and fun they would not pay us money to do it"
    Last edited by Thrasymachus; 02-13-07 at 17:32.
    “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.

  7. #7
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    I figured out an easy way to do this. Basically I check the ID of the last field in my master database. Before I import data I remove the primary key restriction and run an update query whereby I add that last number to each of the existing ID numbers. This automatically iterates my keys to the new start point I need.
    BillS

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Fine. Now make sure you update all the foreign key references to the primary keys you just modified...
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    I forgot to mention to do that immediately afterward, thanks.
    BillS

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sounds like a total fubar process

    how does a surrogate key have any meaning?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    I admit, it's not the best solution--if I understood what you said. But we're only talking about three tables.
    BillS

Posting Permissions

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