By: Rick Dobson | Updated: 2016-08-18 | Comments (4) | Other Database Platforms
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.
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.
- Create and verify the operation of a Linked Server in a 64-bit SQL Server 2014 server pointing at an Oracle 11g database.
- 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.
- Run SQL code via the SQL Server Linked Server to create a target table in Oracle to receive rows of data from SQL Server.
- 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.
- 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
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
- 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.
Last Updated: 2016-08-18
About the author
View all my tips