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

• jujusa2016

  SSC Veteran

  Points: 245

  More actions

  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

• Eric M Russell

  SSC Guru

  Points: 125595

  More actions

  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

• jujusa2016

  SSC Veteran

  Points: 245

  More actions

  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?