Solved

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 ?
This video shows how to recover a database from a user managed backup
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

829 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