Copy data from one database to another using VB.NET

Question

I need to copy data from one database to another using a VB.NET program. The target database is SQL Server the source database is some proprietary ODBC compliant database.

I need to loop through a list of table to copy. Read the data from the source database table for a given modified date. Delete the corresponding date from the target database table and insert the records from the source table. The databases are of the same structure i.e. table names and field names, but the data types may differ (however they are compliant e.g. double in source, float in target). No primary keys exist.

Heres how I may do it :

Firstly execute a Delete command to the target. I could then use a DataReader to obtain data from the source, loop through the Items and create an Insert Command for each row. Add Parameters to the Command with the appropriate values and execute. And wrap the whole thing in a Transaction.

I was just wondering if I am missing a trick here. Any Suggestions

Answer 1

I think you should use the right for the job and I'm guessing that that is SSIS in this case, but I could be wrong and perhaps you have already explored that path.

In that case yes a datareader would do depnding how much data you have. A datatable might even be eassier and faster to program (no need to worry about datatypes since the adapter should take care of that.

Answer 2

The trick would be to use set based operations and not the 'row at a time' concept which we programmers were first taught :)

Here's some pseudocode

INSERT INTO DestTable (columns, columns...)
(Select ModifiedRow from SourceTable where date = Modified)

Perhaps your requirements are more complicated and may need the row by row approach, but this is normally not the case.

I'd opt to put this code in a job step and schedule on SQL. It could also be a stored procedure run from .net.

Also, using SSIS for a db to db transfer is most likely overkill unless you are going to be using some of the special transformations in there.

Answer 3

Take a look at the SqlBulkCopy class. If you can get the source into a DataTable or read it with an IDataReader then it's eligible. It will also attempt to convert between compatible types. See Single Bulk Copy Operations for more details.

This would be more desirable than using INSERT statements for each row.