Solved

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

Posted on 2014-11-07
4
192 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

685 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