Solved

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

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

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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.
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 take different types of Oracle backups using RMAN.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

617 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