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

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 开发者_如何学Gowant 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.

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)

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.

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.