3

In SQL Server 2012 or greater, what is the best “free or almost free” way to copy the data from a set of tables to another where you can overwrite all the destination data but relationships are present so the copy needs to occur in a specific order?

SSIS does not seem to have a way to accomplish this where it could figure out the relationships in the data and delete/copy the data in the correct order when FKs and relationships are present?

Basically I’m trying to copy the data for Table A, B, C, D, E, etc. which may be related to each other and say take all the data from the source and try to overwrite and delete the data in the destination.

jon333
  • 821
  • 2
  • 16
  • 28
  • `TRUNCATE` followed by an `INSERT ... SELECT`? Otherwise, restoring tables individually? – Kermit Oct 23 '13 at 18:47
  • 7
    I don't know that there is a free or automatic way to do it except to write the commands in the proper dependency order (and good luck doing that if you have circular references). The other idea is to drop the relationships at the other end, move the data, then re-create them. – Aaron Bertrand Oct 23 '13 at 18:47
  • Couldn't you do this with Import/Export wizard, one table at a time. Starting with the table that doesn't have an FK (assuming one exists) and working your way backward? The destination tables should be empty and you should enable identity insert. – Khan Oct 23 '13 at 18:49
  • 1
    I would start with a diagram. Start with the table that don't have any relationship pointing in. If you have a self reverence on a table then you will need to sort the data. I would sort all data by PK to minimize fragmentation. And you will need to delete not truncate the tables at are the FK (or disable the FK, truncate, and rebuild the FK - way faster on a big table). – paparazzo Oct 23 '13 at 18:54
  • How about temporarily disabling the fk constraints? Then the table order wouldn't matter. See http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql – Keith Oct 24 '13 at 23:53
  • Remember if you disable the constraint rather than drop it to enable it again properly so that it is trusted, otherwise SQL server won't use the constraints to build the best execution plan that it can: http://www.brentozar.com/blitz/foreign-key-trusted/ – steoleary Oct 25 '13 at 09:40

1 Answers1

0

I think existing SQL 2012 functionality will accomplish your needs nicely. You need to use the **Generate and Publish Scripts Wizard** is SQL Server Management Studio (SSMS) to generate a script you can re-use in SSIS.

1. In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Generate and Publish Scripts. Follow the steps in the wizard to script the database objects for publishing.

2. On the Choose Objects page, select the objects to be published.

3. On the Set Scripting Options page, select Save scripts to a specific location.

a. To specify advanced publishing options, select the Advanced button.

i. Script DROP and CREATE: True

ii. Types of Data to script: Schema and Data

b. On the Summary page, review your selections. Click Previous to change your selections. Click Next to publish the objects you selected.

4. On the Save or Publish Scripts page review to progress of the operation.

You can use the developed script for your SSIS process.

Community
  • 1
  • 1
Derrick Bell
  • 2,615
  • 3
  • 26
  • 30