Solved

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

Posted on 2014-11-07
4
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 34

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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