Bulk Copy Data from Oracle to SQL Server

By: Jeffrey Yao   |   Updated: 2017-10-26   |   Comments (11)   |   Related: > PowerShell

Problem

Copying data from an Oracle table to a SQL Server table is not as simple as you would expect especially when you need to consider performance and flexibility to handle multiple sources.  Is there a convenient and quick way to accomplish moving data from Oracle to SQL Server?

Solution

There are multiple ways to bulk copy data from Oracle to SQL Server. In general, there are four options that I think SQL Server Professionals would be aware of:

1. Use a tool such as Microsoft SQL Server Migration Assistant for Oracle.
2. Use SQL Server Integration Services (SSIS)
3. Create a Linked Server on SQL Server pointing to the Oracle database
4. Export Oracle data to a CSV file and then import the data to SQL Server via bulk copy

Each option has its own pros and cons, but to me all these methods have one shortcoming in common, i.e. time-consuming to setup and develop.

In this tip, we will try to address this shortcoming by using PowerShell and the Oracle Data Provider for .Net, i.e. ODP.net, to quickly develop a solution to copy data from Oracle to SQL Server.

Before we start, we need to install ODP.net, which can be downloaded from here.

ODAC Installation

I downloaded the zip file and then extracted the contents to a folder and here is what I had after extraction:

After extraction, start a Command Prompt as administrator as shown below.

Run the following command in the extracted folder:

install.bat odp.net4 c:\oracle odac true

Here is the screen shot:

After this, I will have all odp.net drivers installed under in my c:\oracle\ folder.  Actually all I need is just one driver, which is c:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll.

Before we can use this dll in PowerShell, we need to unblock it. We can right-click it and then tick the “Unblock” checkbox as shown below:

Actually, I can run the following PowerShell script to unblock automatically:

Dir c:\oracle\odp.net\managed\common\*.dll | unblock-file

Data Preparation

Now we will prepare some test data. I actually installed an Oracle 11g R2 Express edition and Oracle SQL Developer tool on my laptop. In the SQL Developer tool, I can connect to my local Oracle database as shown below:

For demo purposes, I composed the following PL/SQL to create and populate a table [tblTest] in Oracle.

create table tblTest (id INTEGER, dt DATE , name varchar2(100), sales NUMBER(8,2));
declare id integer := 0;
BEGIN
     while id < 1000 loop
          insert into tblTest (id, dt, name, sales)
            values (id, TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '1900-01-01','J')
                                    ,TO_CHAR(DATE '2099-12-31','J')
                                    )
                    ), 'J')
               , dbms_random.string('A', dbms_random.value(5, 99))
               , trunc(dbms_random.value(0.00, 1000.00),2));
        id := id + 1;
end loop; 
commit work;
END;

Let’s run a SELECT statement to check out the data:

select * from tblTest;

We will see the table is indeed populated:

On the SQL Server side (I have a SQL Server 2016 instance on my laptop), I will also create a corresponding table, note, in the Oracle table we have a [sales] column with a data type of NUMBER(8,2) and in SQL Server, I just changed it to a FLOAT data type for that column. I initially tried to use a DECIMAL(8,2) data type, but found this may cause some rounding issues for some records. For example, in Oracle, it shows 841.14, but after copying the data to SQL Server, the value becomes 841.13.

For detailed mapping between Oracle and SQL Server data types, please check the reference links in the Next Steps section.

use MSSQLTips  -- assume this MSSQLTips database exists
create table dbo.tblTest (id int, MyDate date, name varchar(100), sales float;

Data Transfer via PowerShell

By now, we have prepared both tables in Oracle and SQL Server, since the two tables are exactly the same in column sequence and compatible data types, we will first do a quick data transfer from Oracle to SQL Server, this is a table-wise transfer, i.e. each source column will be copied to its corresponding target column.

The script is actually very simple and short (10 lines without comments):

#copy table from Oracle table to SQL Server table
add-type -path 'C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll';
#define oracle connection string
$conn_str = "User Id=system;Password=MyP@ssw0rd;Data Source=XE";
# query for oracle table
$qry = "select * from tblTest";
$adapter = new-object Oracle.ManagedDataAccess.Client.OracleDataAdapter($qry, $conn_str);
$dtbl = new-object System.Data.DataTable('tblTest');
#this Fill method will populate the $dtbl with the query $qry result
$adapter.Fill($dtbl);
#define sql server target instance
$sqlconn = "server=localhost\sql2016;database=mssqltips;trusted_connection=true";
$sqlbc = new-object system.data.sqlclient.Sqlbulkcopy($sqlconn);
$sqlbc.DestinationTableName="dbo.tblTest";
$sqlbc.WriteToServer($dtbl); 

After executing, let’s run the following SELECT query in SSMS:

use mssqltips
select * from dbo.tblTest;

I will get exactly the same result as we get from the Oracle table (of course, the date value is a little bit different):

Since the SQLBulkCopy class is very flexible, we can handle even more complex business requirements, such as when the source and the target tables may not be exactly the same. For example, my target table has more columns and/or the column sequence is not the same as the source Oracle table.

Let’s create the following table:

use MSSQLTips;
create table dbo.tblTest2 (id int identity primary key
, Sales float, Name varchar(100), SaleDate date
, LogDate datetime default current_timestamp);

In this case, we need to make the following column copy:

We can use the following PowerShell script:

#copy table from Oracle table to SQL Server table
add-type -path 'C:\oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll';
#define oracle connectin string
$conn_str = "User Id=system;Password=MyP@ssw0rd;Data Source=XE";
# query for oracle table
$qry = "select dt, sales, name from tblTest";
# key (on the left side) is the source column while value (on the right side) is the target column
[hashtable] $mapping = @{'DT'='SaleDate'; 'SALES'='Sales'; 'NAME'='Name'};
 
$adapter = new-object Oracle.ManagedDataAccess.Client.OracleDataAdapter($qry, $conn_str);
$dtbl = new-object System.Data.DataTable('tblTest');
#this Fill method will populate the $dtbl with the query $qry result
$adapter.Fill($dtbl);
#define sql server target instance
$sqlconn = "server=localhost\sql2016;database=mssqltips;trusted_connection=true";
$sqlbc = new-object system.data.sqlclient.Sqlbulkcopy($sqlconn);
$sqlbc.DestinationTableName="dbo.tblTest2";
 
#need to tell $sqlbc the column mapping info
foreach ($k in $mapping.keys)
{
    $colMapping = new-object System.Data.SqlClient.SqlBulkCopyColumnMapping($k, $mapping[$k]);
    $sqlbc.ColumnMappings.Add($colMapping) | out-null
}
$sqlbc.WriteToServer($dtbl);

Now if I run a SELECT statement on this new table, I will get exactly what is expected:

Summary

In this tip, we learned how to copy data from Oracle to SQL Server via ODP.Net and PowerShell (Version 3+). The main advantage of this method, due to its small code footprint, is speed and flexibility in development plus low maintenance as compared to other approaches, especially SSIS in my opinion.

In some scenarios, such as dumping Oracle data to staging tables in SQL Server, we can further automate this approach by dynamically creating staging tables (i.e. without creating SQL Server tables first) or if we want to dump Oracle data to a CSV file, we can also easily achieve it with this approach.

In short ODP.Net provided a good channel for PowerShell enthusiasts to communicate with Oracle databases and have an elegant data exchange with SQL Server systems.

Next Steps

You can create a cmdlet based on the example in this tip and design the cmdlet parameters with your business requirements. For example, if the source and target table are the same in columns, the parameters can be just a table name (if source and target table names are the same).  However, if you need to select some source columns and copy to some target columns, you may need to add a parameter for column mapping information.  If you dump the data to a CSV file, you may add a parameter for the target CSV location.

You may also review the following articles to further understand the pros and cons of other approaches:

• Creating a SQL Server 2014 Linked Server for an Oracle 11g Database
• Transferring Data Between SQL Server 2014 and Oracle 11g Databases
• Data Type Mapping for Oracle Publishers
• Comparing SQL Server and Oracle datatypes

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips