Transferring Data Between SQL Server 2014 and Oracle 11g Databases

By:   |   Updated: 2016-08-18   |   Comments (4)   |   Related: > SQL Server and Oracle Comparison


Problem

I need a demonstration that illustrates step-by-step instructions for copying table rows between a 64-bit SQL Server 2014 database server and an Oracle 11g database server. I need to know how to perform the transfer going both ways - namely, from a SQL Server 2014 database to an Oracle 11g database as well as from Oracle 11g to SQL Server 2014.

Solution

It is common for BI developers to use SSIS for transferring data between two heterogeneous database servers, such as SQL Server and Oracle. Unfortunately, Visual Studio for SQL Server 2014 was never released by Microsoft in a version that supported transfers between 64-bit versions of SQL Server and Oracle databases. Because Oracle 11g is a 64-bit database, it requires 64-bit transfer technology. See these two links for additional documentation and user commentary about SSIS not supporting 64-bit transfers between SQL Server 2014 and Oracle 11g.

There is, however, a workaround. You can use a Linked Server from within a 64-bit SQL Server 2014 instance to enable the transfer of table rows between SQL Server 2014 and Oracle 11g. The Linked Server in SQL Server 2014 pointing at an Oracle 11g database permits the execution of DDL statements to create and drop tables as well as DML statements for selecting and inserting data from one database to another.

Here's a top level summary of the demonstration in this tip for moving data from a 64-bit SQL Server 2014 database server to an Oracle 11g database and back again.

  1. Create and verify the operation of a Linked Server in a 64-bit SQL Server 2014 server pointing at an Oracle 11g database.
  2. Gain access to a table with rows you will be copying between the servers. For example, download the backup file and restore the sample database for this tip.
  3. Run SQL code via the SQL Server Linked Server to create a target table in Oracle to receive rows of data from SQL Server.
  4. Populate the freshly created Oracle table with data from SQL Server and verify that the copied values in Oracle match the original source data in SQL Server.
  5. Copy data from Oracle to SQL Server and verify the copied values from Oracle match the original source data from SQL Server.

Create a SQL Server Linked Server to Access Oracle

A prior MSSQLTips.com article, Creating a SQL Server 2014 Linked Server for an Oracle 11g Database, provides step-by-step instructions for creating, configuring, and verifying a Linked Server in SQL Server 2014 that points to an Oracle 11g database. The tip demonstrates how to unlock an account for the HR schema in the sample database that ships with Oracle 11g and also reveals how to use the Linked Server in a simple SELECT query statement. The linked server's name in the tip is OrclDB.

After setting up the Linked Server, you should be able to verify that the following query returns 3 rows of data from the EMPLOYEES table in the HR schema of the sample database. This result set verifies the Linked Server is interacting properly with the sample Oracle database. See the original article on a Linked Server for Oracle 11g to view the contents of the result set. 

-- No database context is required for this query
-- but the OrclDB linked server must be created as described in
-- Creating a SQL Server 2014 Linked Server for an Oracle 11g Database tip

-- Confirm the successful creation of the OrclDB linked server
-- It returns 3 rows
-- DO NOT RUN SUBSEQUENT QUERIES UNTIL THIS QUERY SUCCEEDS

-- Required caps for schema and table names
SELECT TOP 3 * FROM OrclDB..HR.EMPLOYEES

Setup Demonstration to Move Data

Download the backup file (SSandOracleDataExchange.bak) associated with this tip. The backup file is for a database with just one table containing 200,000 rows with three columns named FIRST_NAME, LAST_NAME, and BIRTH_DATE. The table was created in a database named SSandOracleDataExchange. The name of the table is SQL_SERVER_DATA_FOR_ORACLE.

You can use the following script to restore the SSandOracleDataExchange database on your 64-bit SQL Server 2014. The script conditionally removes a copy of the database if it already exists on the server. 

-- Conditionally drop and then restore source database 
-- with data for copying between SQL Server and 
-- Oracle database servers
USE [master]
GO

IF EXISTS(select * from sys.databases where name='SSandOracleDataExchange')
DROP DATABASE SSandOracleDataExchange

RESTORE DATABASE SSandOracleDataExchange FROM  
DISK = N'C:\SSandOracleDataExchange\SSandOracleDataExchange.bak'

After restoring the database, you can run a few queries to familiarize yourself with the table within the database.

  • The first query below shows column values for three rows from the SQL_SERVER_DATA_FOR_ORACLE table (see the result set in the screen shot below). First and last name values are derived from cross joining a subset of US census data on first and last names. Birth dates were randomly assigned within an arbitrary range to the names.
  • The second query confirms that there are 200,000 rows in the SQL_SERVER_DATA_FOR_ORACLE table.
  • The third query verifies that all 200,000 rows in the source table are unique by the combination of FIRST_NAME, LAST_NAME, and BIRTH_DATE column values.
USE SSandOracleDataExchange
GO

-- Show values for 3 rows from the 
-- SQL_SERVER_DATA_FOR_ORACLE table
-- in the SSandOracleDataExchange database
SELECT TOP 3 *
FROM [SQL_SERVER_DATA_FOR_ORACLE]

-- There are 200000 rows in the 
-- SQL_SERVER_DATA_FOR_ORACLE
SELECT COUNT(*) Count_of_rows_in_SQL_SERVER_DATA_FOR_ORACLE 
FROM [SQL_SERVER_DATA_FOR_ORACLE]

-- Each of the 200000 rows has a distinct 
-- set of FIRST_NAME, LAST_NAME, and BIRTH_DATE values
SELECT COUNT(*) Count_of_distinct_rows_in_SQL_SERVER_DATA_FOR_ORACLE
FROM
(
SELECT 
DISTINCT  
 FIRST_NAME
,LAST_NAME
,BIRTH_DATE
FROM [SQL_SERVER_DATA_FOR_ORACLE]
) for_distinct_rows

Transferring_Rows

Create Target Table in Oracle to Receive Rows from SQL Server

The next step in the demonstration is to create a table in the Oracle 11g sample database to receive rows from the SQL_SERVER_DATA_FOR_ORACLE table in SQL Server. Both SQL Server and Oracle use a CREATE TABLE statement for creating a new database. This statement will fail if the name for the table you try to create already exists in the same schema of the database. Therefore, you should check if the name for new table is assigned to an existing table in the schema before invoking the CREATE TABLE statement.

Using our OrclDB Linked Server, there are at least two methods of seeing if a table name already exists in the HR schema of the sample database to which the Linked Server connects. First, we can enumerate the table name of each user table in the HR schema; use a WHERE clause with the name of the prospective new table to determine if the name for the new table is already assigned to an existing table. The p1 value in the result set for the following query is 0 when the new table name is not already assigned to an existing table. The following code sample illustrates the syntax for this approach. The name of the prospective new table in this demonstration is NAMESANDBIRTHDATES. 

-- Displays p1 value 0 if Oracle table does not exist
EXEC (
'
SELECT COUNT(*) p1
FROM
(
SELECT table_name
FROM sys.user_tables
WHERE table_name = ''NAMESANDBIRTHDATES''
)
') at OrclDB

If a table already exists with the name you planned to use for the new table, then you can use a different name for the new table, rename the existing table, or simply drop the existing table. The last approach is particularly convenient when the existing table contains obsolete staging data that are no longer needed. The following script shows how to drop an existing table with the Linked Server. If the table does not already exist, an Oracle message appears in the SSMS Messages pane indicating the table does not exist along with an error message from SQL Server for the EXEC statement. If the table does already exist, then the SSMS Messages pane displays that 0 rows are affected, and there is no error message from SQL Server. 

-- Drop Oracle table if it does already exists
EXEC ('DROP TABLE NAMESANDBIRTHDATES') at OrclDB

After making the name NAMESANDBIRTHDATES available for a new table in the sample database, you can invoke the following SQL code create a new table. The Oracle VARCHAR2 data type holds a variable length character value up to 4,000 characters with one byte per character. The DATE data type holds a datetime value in both Oracle and SQL Server. 

-- Create Oracle table for holding data exported 
-- from SQL Server
EXECUTE  (
'
CREATE TABLE NAMESANDBIRTHDATES(
First_Name varchar2(50)
,Last_Name VARCHAR2(50)
,Birth_Date DATE)
'
)
at OrclDB

Transfer Data from SQL Server to Oracle

After you create a target table in Oracle to receive data from SQL Server, you can use an INSERT statement followed by a SELECT statement to populate the table you created in Oracle. The INSERT statement references the Linked Server in SQL Server along with the schema name and target table name in Oracle. The SELECT statement designates the table in SQL Server from the source table (SQL_SERVER_DATA_FOR_ORACLE) in the source database (SSandOracleDataExchange). The script below implicitly references the source database from a previously invoked USE statement that sets the database context to SSandOracleDataExchange. If the SELECT query needs data from a different schema than the default schema, then specify the other schema name as a prefix to the table name. 

-- Insert data into Oracle table
-- from SQL Server table
INSERT INTO OrclDB..HR.NAMESANDBIRTHDATES
SELECT [FIRST_NAME]
      ,[LAST_NAME]
      ,[BIRTH_DATE]
FROM [SQL_SERVER_DATA_FOR_ORACLE]

The INSERT and SELECT statement pair above displays the number of rows affected in the SSMS Messages pane. For this demonstration, the number of rows affected is 200,000. On the computer used in the demonstration, the INSERT and SELECT statement pair completed in 2 minutes and 55 seconds.

The next query verifies the number of matching rows from the loaded table in the Oracle database compared to the original source table in the SQL Server database. The count of matching rows is 200,000. This outcome confirms that all rows from the source table in SQL Server copied successfully to the target table in Oracle. The verification query completed in 3 seconds. 

-- Verified all rows in Oracle table match rows
-- in SQL Server table
SELECT COUNT(*) [Count of rows matched from SQL Server to Oracle]
FROM
(
SELECT * FROM OrclDB..HR.NAMESANDBIRTHDATES

INTERSECT

SELECT [FIRST_NAME]
      ,[LAST_NAME]
      ,[BIRTH_DATE]
FROM [SQL_SERVER_DATA_FOR_ORACLE]
) matching_rows

Transfer Data from Oracle to SQL Server

The following script shows a way to copy rows from a table in Oracle to a table in SQL Server. The script starts by conditionally dropping a target table (ORACLE_DATA_FOR_SQL_SERVER) in SQL Server. Notice that the syntax in this script uses the INTO clause of a SELECT statement to populate the target table. This eliminates the need to run a CREATE TABLE statement. As with going from SQL Server to Oracle, this script adds 200,000 rows to a new table. In terms of performance, the SQL Server target table is populated in just 5 seconds. 

-- Drop SQL Server table for receiving rows
-- from Oracle if it already exists
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'ORACLE_DATA_FOR_SQL_SERVER')
DROP TABLE ORACLE_DATA_FOR_SQL_SERVER

-- Copy rows from Oracle table to SQL Server table
-- Required caps for schema and table names
SELECT * INTO ORACLE_DATA_FOR_SQL_SERVER FROM OrclDB..HR.NAMESANDBIRTHDATES

It is straightforward to verify the data transfer by comparing the rows in the ORACLE_DATA_FOR_SQL_SERVER table to the original source data in the SQL_SERVER_DATA_FOR_ORACLE table. This test assumes the values in the ORACLE source table already matched rows in the original SQL Server source table (which was confirmed previously). The following query demonstrates how to accomplish this. All 200,000 rows match perfectly for the comparison of the ORACLE table values to the original source SQL Server table values; this verification query completes in 1 second. 

-- Verified all rows copied from Oracle table match rows
-- in original source SQL Server table
SELECT COUNT(*) [Count of rows matched from Oracle to SQL Server]
FROM
(
SELECT * FROM ORACLE_DATA_FOR_SQL_SERVER

INTERSECT 

SELECT [FIRST_NAME]
      ,[LAST_NAME]
      ,[BIRTH_DATE]
FROM [SQL_SERVER_DATA_FOR_ORACLE]
) matching_rows
Next Steps
  • Use the database backup file and SQL script file associated with this tip to confirm that you can reproduce the results described in the tip.
  • You will also find it helpful to reference a prior tip on creating a Linked Server in SQL Server 2014 for the sample database that ships with Oracle 11g (Creating a SQL Server 2014 Linked Server for an Oracle 11g Database). This will allow you to confirm your ability to use Linked Servers as an alternative to SSIS projects for 64-bit transfers between SQL Server 2014 and Oracle 11g. Recall that this capability is critical because SSIS projects for SQL Server 2014 do not support 64-bit data transfers.
  • Notice that the transfer rate was much faster when going from Oracle to SQL Server than from SQL Server to Oracle. This outcome (200,000 rows transferred in 5 seconds) indicates that the Linked Server approach for data transfer is especially well suited to copying data from Oracle to SQL Server. The slower performance for transferring rows from SQL Server to Oracle (200,000 rows transferred in 2 minutes and 55 seconds) may or may not be an acceptable long-run solution for your production row transfer requirements. If not, then you can at least use the linked server approach described here temporarily while you search for a faster alternative.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rick Dobson Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

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

View all my tips


Article Last Updated: 2016-08-18

Comments For This Article




Friday, August 19, 2016 - 1:43:24 PM - Rick Dobson Back To Top (43150)

I agree that moving data from SQL Server to Oracle via a linked server is not fast -- to say that it is painfully slow, depends on the volume of data that you are trying to move and what's an acceptable timeframe for the move to occur.  To me and many other SQL Server BI developers, linked servers are familiar technology.  Also, using them is kind of second nature.  Also, I want to point out that using linked servers to move data from Oracle to SQL Server is dramatically faster than for from SQL Server to Oracle.  It is nice to have one technology to go both ways that is user-friendly to sql BI developers.

That said, I did develop an alternative that is in the publication queue that is dramatically faster.  This faster approach, which does not depend on linked servers or Python takes advantage of Oracle technology for getting data from SQL Server into Oracle.  I hope this other approach will appeal to at least some sql BI developers who find linked servers too slow for moving data from SQL Server to Oracle. 

 

 

Friday, August 19, 2016 - 11:28:34 AM - thesqlist Back To Top (43149)

I wouldn't recommand using linked server from SQL to Oracle 11. I tested that only to see how it behaves to note that was painfully slow especially if the Oracle server is located in a remote location, as it was in my case. The best way si to use the SSIS features or even better to use a language like Python, or other of your choice, and have full control over it. Personally I use Python which works beutifully simple and nice. Also it will take care of encoding incompatibilities, which can occure in the process, as actually happended to me. In my case I copy from Oracle to insert in SQL server. Another issue that will be solved by this, again that applied in my case, is the transfer of spatial data which required a change of projection. In SQL you can NOT do that but in Python is possible.

Regards.

Friday, August 19, 2016 - 10:38:19 AM - Rick Dobson Back To Top (43148)

 Hi Pani,

Good question.

I have not personally looked into that topic yet.  Here's a URL that I found that talked about SQL Server and Oracle TDE functionality which allowed the downloading of a white paper: http://info.townsendsecurity.com/bid/57413/Oracle-SQL-Server-and-Encryption-Key-Management

As a general point on SQL Server and Oracle interoperability, my sql development experience is that they are generally the same but distinct in many subtle ways.  These differences can often cause issues that require work-arounds.  It is all do-able stuff, but ...  If TDE is like sql development, I am guessing you can expect to encounter issues. Another interesting point on TDE comparability, is whether it is even works with a linked server.

I have a forthcoming tip in queue for publication that will show a non-linked server approach for moving data from Oracle to SQL Server.  While this other approach is not as simple to use as a linked server, it will may have a better chance of by-passing comparability issues between sql server and oracle implementations of tde.

Rick Dobson

 

 

Thursday, August 18, 2016 - 4:15:30 PM - Pani Back To Top (43142)

Hi Rick,  Thanks for the post.

If SQL Server 2014 is TDE enabled, this will work?.

Is their any way TDE enabled database tables can replicate to Oracle, without third party tools.

 

Thanks,

Pani

 














get free sql tips
agree to terms