Can I use DataTables to import data from one database into another?

Question

I need to create a quick and dirty solution to migrate data from database into another. This is only being used a proof of concept. Long term we will use .NET's Sync Framework.

The databases are identical. The solution is going to be used as an OCA (occasionally connected application).

• I read in which tables they want to migrate from some XML.
• Disable all constraints on the target for each table.
• For each table they want to migrate data from I create a DataTable from the source.
• Create a DataTable pointing to the target.
• Import all the rows from the source into the target and insert them
• Enable all constraints on the target tables again.

I am not sure if the above is possible. I had most of it working and I was cloning the source DataTable. I then had the problem where the cloned DataTable wasn't pointing anywhere.

• Can I point it to the target and then insert?
• Is there a better way to do this?
• The alternative is to create INSERT INTO statements, using metadata to identify identity columns and not include them in the column names.

Answer 1

What you're proposing should work. But you might find it easier (and you'll definitely see better performance) with the SqlBulkCopy class.

(This is a code-focused solution)

Answer 2

You can use the Sql Server Import and Export Wizard (dtswizard.exe). It creates an Integration Services package that you can then save and execute whenever you want.

Answer 3

Short answer: You can load your DataTable and save it into another database by using a different DataAdapter.

But, for a code less approach, you can to use SQL Server Database Publishing Toolkit as stated here.