SQL Server: export data via SQL query?

Question

I have FK and PK all over my db and table data needs to be specified in a certain order or else I get FK/PK insertion errors. I'm tired of executing the wizard again and again to transfer data one table at a time.

In the SQL Server export data wizard there is an option to "Write a query to specify the data to transfer". I'd like to write the query myself and specify the correct order.

1. Will this solve my problem?

2. How do I do this? Can you provide a sample query (or link to one)

   • The databases are on two different servers - SQL Server 2008 on each ; The database names & permissions are the same ; each table name & col is the same ; I need Identity Insert for each table.

Answer 1

Disable foreign keys before importing, enable them after the import:

ALTER TABLE tablename NOCHECK CONSTRAINT ALL

ALTER TABLE tablename WITH CHECK CHECK CONSTRAINT ALL

Update: Thanks for the comments, I fixed the syntax.

Answer 2

You could always save the package and then open and edit the package to put things in the right order (you might have to copy the data flow several times and put dependencies between them)

Answer 3

You can use 3rd party tools to transfer a data; these tools disable/enable constraints automatically.