Solved

vb.net fastest way to insert from oracle to sql server

Posted on 2014-11-07
4
180 Views
Last Modified: 2015-09-11
Hi,

I have a vb.net program that runs a query against an Oracle database, opens a transaction to a Sql Server database, then inserts all records from the Oracle database query into Sql Server. Current, I am using the code below and the loop to insert data takes a very long time compared to just the actual query to Oracle.  what is the fastest way to insert these records to sql Server?  

Dim cnx As New OracleClient.OracleConnection(connectString)
Dim cnxSqlServer As New System.Data.SqlClient.SqlConnection
Dim cmd As New OracleClient.OracleCommand()

cmd.Connection = cnx
cnx.Open()
cmd.CommandText = sql
cmd.Connection = cnx
cmd.CommandType = CommandType.Text

cnxSqlServer.ConnectionString = connectStringSQLServer
cnxSqlServer.Open()
 Dim transaction = cnxSqlServer.BeginTransaction()
  Dim reader As OracleClient.OracleDataReader = cmd.ExecuteReader()
        Try
            If reader.HasRows Then
                Do While reader.Read()
                    INSERT INTO SQL Server Table
                Loop
               transaction.Commit()
         End If

        Catch sqlex As SqlException
               transaction.Rollback()
        Catch ex As Exception
               transaction.Rollback()
       End Try
0
Comment
Question by:FDC2005
4 Comments
 
LVL 33

Expert Comment

by:it_saige
ID: 40428944
Have you considered using an SQL Bulk Copy?

http://mytipsfordotnet.blogspot.com/2011/05/sql-bulk-copy.html

-saige-
0
 

Author Comment

by:FDC2005
ID: 40429460
I did try bulk copy but it didn't turn out to be an faster for the number of records that are being inserted from Oracle to Sql Server.
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40429938
Hi,
You can also create linked server to connect to Oracle, within Sql server, and then use openquery to select records from Oracle DB. Read
http://msdn.microsoft.com/zh-tw/library/ms188427.aspx
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 40430522
Using VB is probably the slowest way of doing this.
Use SSIS to pull the data out of Oracle and into SQL Server. That's what it's designed to do! And you can deploy and then schedule the package to run when it's convenient if you have to do it daily (for example).

hth

Mike
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now