Post reply

How to move the records of one database(normalized 18 tables with foreign key constraint) to another database server.

  • June 9, 2016 at 3:38 pm

    #327442

    We have designed a normalized data model that has around 18 tables in it and the tables are inter linked with each with foreign key constraints.

    This is a operational data model that will be constantly updated and inserted.

    but for reporting purpose(to generate reports), we are planning to move the older records to another dedicated database server. i would like to move the entire database structure to another database server(all 18 tables with foreign key constraints) to another table

    and write queries to display reports.

    how to achieve this?

    just for simplicity i am giving 4 tables here. each one are connected to other with foreign key

    order table -

    this contains all the order information

    OrderID OrderNumber customername

    ===========================

    PK

    device table -

    each order can have multiple devices

    DeviceID DeviceName DeviceModel OrderID

    ==========================

    PK FK

    jobs -

    each devices can have multiple jobs running

    JobID deviceID JobNumber

    ===============

    PK FK

    job status - displays the job status(progress, completed, failed)

    JobStatusId JobID jobStatus

    ===================

    PK FK

  • June 9, 2016 at 4:12 pm

    #1884264

    You can use SSIS or the SSMS Import Data wizzard to copy from source table to target table. You'll want to disable foreign keys on all target tables before the data load, run the data loads in parallel, and then re-enable the foreign keys afterward. Disabling foreign keys during data load will prevent the need to perform inserts in a regid sequence, the data load will run faster, and you can perform in parallel.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • June 9, 2016 at 4:21 pm

    #1884265

    Thanks Eric.

    But then i want to do this periodically(say every night). For example, as and when the jobs are completed for a particular order, we need to move them to reporting database. only those jobs that are still running will remain the operational database

    is there a way we can achieve tat using SSIS?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply