Results 1 to 5 of 5

Thread: Database Append

  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Unanswered: Database Append

    I would highly appreciate help to :
    I have two databases that are of the same schema but in one-database-A the records are a bit older than the other database-B. I would lilke to sync the records from database-B to database-A appending the records to database-A. The schemas have constraints,primary keys and other defaults in common.

    What is the best way to do this task? I tried to use DTS but it fails throwing error Primary key violations on the tables.

    Thanks a lot for a good suggestion.
    Vinnie

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    This really depends on what other tables depend on this table. You will need to post any relationships/dependencies to this table. Also, define your constraints. How is the primary key generated/defined for both tables ?
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134
    It is not one table but all tables in database. I can not drop or truncate the destination database table data as there may be student number child data in the source database tables that does not exist in old database. Do I have to write a script taking into consideration each table and it's dependencies or PK an FK and other constraints? That may take hell a lot of time to figure out. Any better suggestion than this thought is highly appreciated.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    What are some specific examples of the data that resides in both databases - what happens when you have 2 identical records in the same table in both databases - what is the relationship (or is there) of the data between the 2 databases (or do you just want to append all old data or do you want to reconcile) - how many parent-child relationships exist ?

    These are not normally simple translations because the same primary key can exist in both databases/tables - if it does then you will have to translate the parent/child keys from the old to the new.
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Depending on your keys, you might be able to query against the new database and find where duplicate keys exist and generate new keys and cascade those new keys to the children. Then migrate all data from old to new - if that is your process.
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

Posting Permissions

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