1

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

Improve this question
0

4 Answers 4

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I agree with chrissie on this one-you should use SSIS to perform your ETL instead of a custom vb.net app. It's much faster and once you get the hang of the ui, much simpler and easier to maintain: msdn.microsoft.com/en-us/library/ms141026.aspx
Thanks, SSIS is not a option, so it has to be VB. To add to the question, could I obtain a datatable from the source via a dataadapter. somehow reattach this datatable to another dataadapter and update the target.
You could probably just do all of it in a SQL Job and stored procedures unless he needs any of the fancy transformation tasks. To many people jump to SSIS when you can just use plain old T-SQL.
0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0 
Dim reader As System.IO.DirectoryInfo
        reader = My.Computer.FileSystem.GetDirectoryInfo("c:\program Files\Microsoft SQL Server\MSSQL.1\mssql\data")
        If (reader.Attributes And System.IO.FileAttributes.ReadOnly) > 0 Then
            MsgBox("File is readonly!")
        Else
            MsgBox("Database is not read-only protected")
        End If

Check all the tables first

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.