Transferring large datasets between Oracle and SQL Server

This guide demonstrates various methods for transferring large datasets between Oracle and SQL Server using the Easysoft ODBC-SQL Server Driver. The examples show performance optimizations and best practices for handling millions of records efficiently.

Test environment

All tests were performed using two Oracle Virtual Machines on the same physical computer:

SQL Server 2019 running on Windows Server 2019
Oracle 23ai on Oracle Linux Server release 8.10
Easysoft ODBC-SQL Server Driver version 2.1.17

Creating test data in SQL Server

To ensure reproducible results, we used the SQL Server sample database AdventureWorks2019. You can download it from:

https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.ba

Initial data setup

First, create the initial dataset by extracting product data from the AdventureWorks database:

SELECT
    "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
    "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
    "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
    "DaysToManufacture", "ProductLine", "Class", "Style",
    "ProductSubcategoryID", "ProductModelID", "SellStartDate",
    "SellEndDate", "DiscontinuedDate", "ModifiedDate"
INTO Products 
FROM Adventureworks2019."Production"."Product"

This creates a table with 504 rows. To generate a larger dataset for performance testing, duplicate the rows using this loop:

DECLARE @cnt INT = 0;
WHILE @cnt < 12
BEGIN
    INSERT INTO Products (
        "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
        "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
        "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
        "DaysToManufacture", "ProductLine", "Class", "Style",
        "ProductSubcategoryID", "ProductModelID", "SellStartDate",
        "SellEndDate", "DiscontinuedDate", "ModifiedDate"
    )
    SELECT 
        "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
        "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
        "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
        "DaysToManufacture", "ProductLine", "Class", "Style",
        "ProductSubcategoryID", "ProductModelID", "SellStartDate",
        "SellEndDate", "DiscontinuedDate", "ModifiedDate" 
    FROM Products;
    
    SET @cnt = @cnt + 1;
END;

Result: 2,064,384 total rows for performance testing

Preparing for bidirectional testing

If you plan to test data insertion from Oracle back to SQL Server, convert NOT NULL columns to NULLABLE:

DECLARE @TableName NVARCHAR(128) = 'Products';
DECLARE @SchemaName NVARCHAR(128) = 'dbo';
DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL += 
    'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ' +
    'ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE +
    CASE
        WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
             AND DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
        THEN '(' + 
             CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
                  ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ')'
        WHEN DATA_TYPE IN ('decimal', 'numeric')
        THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ',' + 
             CAST(NUMERIC_SCALE AS VARCHAR) + ')'
        ELSE ''
    END + ' NULL;' + CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
  AND TABLE_SCHEMA = @SchemaName
  AND IS_NULLABLE = 'NO';

PRINT @SQL;
EXEC sp_executesql @SQL;

Inserting Data into Oracle from SQL Server

This section demonstrates performance optimization techniques for transferring over 2 million records from SQL Server to Oracle. We'll compare different approaches and their performance characteristics.

Method 1: Direct Oracle database link (baseline)

Using SQL*Plus with the Easysoft ODBC-SQL Server Driver, we first establish a baseline performance measurement:

-- Create table structure from SQL Server
CREATE TABLE Products AS (
    SELECT * FROM "Products"@sqlsrv2019 
    WHERE "ProductID" = 1
);

-- Clear the table for testing
DELETE FROM Products;

Performance test with timing:

SELECT CURRENT_TIMESTAMP FROM DUAL;

INSERT INTO Products (
    "ProductID", "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
    "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
    "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
    "DaysToManufacture", "ProductLine", "Class", "Style",
    "ProductSubcategoryID", "ProductModelID", "SellStartDate",
    "SellEndDate", "DiscontinuedDate", "ModifiedDate"
)
SELECT 
    "ProductID", "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
    "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
    "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
    "DaysToManufacture", "ProductLine", "Class", "Style",
    "ProductSubcategoryID", "ProductModelID", "SellStartDate",
    "SellEndDate", "DiscontinuedDate", "ModifiedDate"
FROM "Products"@SQLSrv2019;

SELECT CURRENT_TIMESTAMP FROM DUAL;

Baseline performance: ~6 minutes for 2,064,384 rows

Method 2: BCP + SQL*Loader (optimized)

A significantly faster approach combines Easysoft's BCP tool with Oracle's SQL*Loader for bulk data loading.

Step 1: Extract data using BCP

/usr/local/easysoft/sqlserver/bcp/bcp "SELECT 
    ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color,
    SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, Size,
    SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture,
    ProductLine, Class, Style, ProductSubcategoryID, ProductModelID,
    SellStartDate, SellEndDate, DiscontinuedDate, ModifiedDate 
FROM AdventureWorks2019.dbo.Products" queryout /tmp/pb.txt \
    -S 192.168.6.18 -U test -P test -c -t "~" -r "\n"

Step 2: Create SQL*Loader control file

Create /tmp/pb.ctl with optimized loading parameters:

OPTIONS (DIRECT=TRUE, rows=500000, COLUMNARRAYROWS=500000, STREAMSIZE=500000)
load data
infile '/tmp/pb.txt'
into table Products
fields terminated by "~"
(
    "ProductID" NULLIF "ProductID"=BLANKS,
    "Name" NULLIF "Name"=BLANKS,
    "ProductNumber" NULLIF "ProductNumber"=BLANKS,
    "MakeFlag" NULLIF "MakeFlag"=BLANKS,
    "FinishedGoodsFlag" NULLIF "FinishedGoodsFlag"=BLANKS,
    "Color" NULLIF "Color"=BLANKS,
    "SafetyStockLevel" NULLIF "SafetyStockLevel"=BLANKS,
    "ReorderPoint" NULLIF "ReorderPoint"=BLANKS,
    "StandardCost" NULLIF "StandardCost"=BLANKS,
    "ListPrice" NULLIF "ListPrice"=BLANKS,
    "Size" NULLIF "Size"=BLANKS,
    "SizeUnitMeasureCode" NULLIF "SizeUnitMeasureCode"=BLANKS,
    "WeightUnitMeasureCode" NULLIF "WeightUnitMeasureCode"=BLANKS,
    "Weight" NULLIF "Weight"=BLANKS,
    "DaysToManufacture" NULLIF "DaysToManufacture"=BLANKS,
    "ProductLine" NULLIF "ProductLine"=BLANKS,
    "Class" NULLIF "Class"=BLANKS,
    "Style" NULLIF "Style"=BLANKS,
    "ProductSubcategoryID" NULLIF "ProductSubcategoryID"=BLANKS,
    "ProductModelID" NULLIF "ProductModelID"=BLANKS,
    "SellStartDate" timestamp "YYYY-MM-DD HH24:MI:SS.FF" 
                   NULLIF "SellStartDate"=BLANKS,
    "SellEndDate" timestamp "YYYY-MM-DD HH24:MI:SS.FF" 
                 NULLIF "SellEndDate"=BLANKS,
    "DiscontinuedDate" timestamp "YYYY-MM-DD HH24:MI:SS.FF" 
                      NULLIF "DiscontinuedDate"=BLANKS,
    "ModifiedDate" timestamp "YYYY-MM-DD HH24:MI:SS.FF" 
                  NULLIF "ModifiedDate"=BLANKS
)

Performance tip The OPTIONS line with DIRECT=TRUE and optimized row processing parameters significantly improves SQL*Loader performance.

Step 3: Execute the combined process

date
/usr/local/easysoft/sqlserver/bcp/bcp "SELECT ProductID, Name, ProductNumber..." \
    queryout /tmp/pb.txt -S 192.168.6.18 -U test -P test -c -t "~" -r "\n" > /dev/null
sqlldr system/oracle control=/tmp/pb.ctl > /dev/null
date

Optimized performance: 1 minute 24 seconds for 2,064,384 rows. Performance improvement: ~4.3x faster than baseline method.

Cleanup

exit | sqlplus system/oracle@orcl @/tmp/clean.sql
rm /tmp/pb.txt

Inserting data from Oracle to SQL Server

Limitation: Oracle does not support direct insert operations to remote databases using syntax like:

INSERT INTO "Products"@localsqlsrv2019 SELECT * FROM Products;

This results in error: ORA-02025: all tables in the SQL statement must be at the remote database

Method 1: Row-by-row `INSERT` (not recommended)

While Oracle documentation suggests using array processing with these parameters:

HS_FDS_ARRAY_EXEC = TRUE
HS_FDS_FETCH_ROWS = 100

Testing with PL/SQL cursor loops:

BEGIN
    FOR rec IN (
        SELECT "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
               "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
               "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
               "DaysToManufacture", "ProductLine", "Class", "Style",
               "ProductSubcategoryID", "ProductModelID", "SellStartDate",
               "SellEndDate", "DiscontinuedDate", "ModifiedDate"
        FROM PRODUCTS FETCH FIRST 2000 ROWS ONLY
    ) LOOP
        INSERT INTO "Products"@LocalSQLSrv2019 (
            "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
            "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
            "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
            "DaysToManufacture", "ProductLine", "Class", "Style",
            "ProductSubcategoryID", "ProductModelID", "SellStartDate",
            "SellEndDate", "DiscontinuedDate", "ModifiedDate"
        ) VALUES (
            rec."Name", rec."ProductNumber", rec."MakeFlag", rec."FinishedGoodsFlag",
            rec."Color", rec."SafetyStockLevel", rec."ReorderPoint", rec."StandardCost",
            rec."ListPrice", rec."Size", rec."SizeUnitMeasureCode",
            rec."WeightUnitMeasureCode", rec."Weight", rec."DaysToManufacture",
            rec."ProductLine", rec."Class", rec."Style", rec."ProductSubcategoryID",
            rec."ProductModelID", rec."SellStartDate", rec."SellEndDate",
            rec."DiscontinuedDate", rec."ModifiedDate"
        );
    END LOOP;
    COMMIT;
END;

Issue: Inspecting the unixODBC logs, I could find no evidence Oracle was setting the array size to 100 and DG4ODBC just sent one row at a time.

Method 2: `DBMS_HS_PASSTHROUGH` (single row)

DECLARE
    rows_affected INTEGER;
    v_cur INTEGER;
    v_sql VARCHAR2(1000);
    
    CURSOR C IS
        SELECT "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
               "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
               "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
               "DaysToManufacture", "ProductLine", "Class", "Style",
               "ProductSubcategoryID", "ProductModelID", "SellStartDate",
               "SellEndDate", "DiscontinuedDate", "ModifiedDate"
        FROM PRODUCTS FETCH FIRST 50000 ROWS ONLY;
    
    c_row c%ROWTYPE;
    
BEGIN
    DBMS_OUTPUT.PUT_LINE('Current time: ' || SYSTIMESTAMP);
    
    v_sql := 'INSERT INTO "Products" (
        "Name", "ProductNumber", "MakeFlag", "FinishedGoodsFlag",
        "Color", "SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice",
        "Size", "SizeUnitMeasureCode", "WeightUnitMeasureCode", "Weight",
        "DaysToManufacture", "ProductLine", "Class", "Style",
        "ProductSubcategoryID", "ProductModelID", "SellStartDate",
        "SellEndDate", "DiscontinuedDate", "ModifiedDate"
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
    
    v_cur := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@LocalSQLSrv2019;
    DBMS_HS_PASSTHROUGH.PARSE@LocalSQLSrv2019(v_cur, v_sql);
    
    OPEN C;
    LOOP
        FETCH c INTO c_row;
        EXIT WHEN c%NOTFOUND;
        
        -- Bind all 23 parameters
        DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 1, c_row."Name");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 2, c_row."ProductNumber");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 3, c_row."MakeFlag");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 4, c_row."FinishedGoodsFlag");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 5, c_row."Color");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 6, c_row."SafetyStockLevel");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 7, c_row."ReorderPoint");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 8, c_row."StandardCost");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 9, c_row."ListPrice");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 10, c_row."Size");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 11, c_row."SizeUnitMeasureCode");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 12, c_row."WeightUnitMeasureCode");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 13, c_row."Weight");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 14, c_row."DaysToManufacture");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 15, c_row."ProductLine");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 16, c_row."Class");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 17, c_row."Style");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 18, c_row."ProductSubcategoryID");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 19, c_row."ProductModelID");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 20, c_row."SellStartDate");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 21, c_row."SellEndDate");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 22, c_row."DiscontinuedDate");
	DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23, c_row."ModifiedDate");
        
        rows_affected := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@LocalSQLSrv2019(v_cur);
    END LOOP;
    
    CLOSE C;
    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@LocalSQLSrv2019(v_cur);
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Current time: ' || SYSTIMESTAMP);
END;

Performance: ~1 minute 6 seconds for 50,000 rows.

Method 3: `DBMS_HS_PASSTHROUGH` (five rows per statement)

This leverages SQL Server's support for multi-row INSERT statements to improve performance.

Concept: Instead of executing individual INSERT statements, construct SQL with multiple value sets:

DECLARE
	rows_affected INTEGER;
	v_cur  INTEGER;
	v1_sql  VARCHAR2(1000);
	f_sql  VARCHAR2(1000);
	rp  VARCHAR2(1000);

	CURSOR C IS
		Select 
		"Name","ProductNumber","MakeFlag","FinishedGoodsFlag",
		"Color","SafetyStockLevel","ReorderPoint","StandardCost","ListPrice",
		"Size","SizeUnitMeasureCode","WeightUnitMeasureCode","Weight",
		"DaysToManufacture","ProductLine","Class","Style",
		"ProductSubcategoryID","ProductModelID","SellStartDate",
		"SellEndDate","DiscontinuedDate","ModifiedDate" 
		FROM PRODUCTS FETCH FIRST 50000 ROWS ONLY;

		
	a_row c%ROWTYPE;
	b_row c%ROWTYPE;
	c_row c%ROWTYPE;
	d_row c%ROWTYPE;
	e_row c%ROWTYPE;
	
	prc INTEGER;
	
	start_time timestamp;
	end_time timestamp;
	dur_sec NUMBER;
	
	    
BEGIN

    v1_sql := 'INSERT INTO
    "Products" (
        "Name",
        "ProductNumber",
        "MakeFlag",
        "FinishedGoodsFlag",
        "Color",
        "SafetyStockLevel",
        "ReorderPoint",
        "StandardCost",
        "ListPrice",
        "Size",
        "SizeUnitMeasureCode",
        "WeightUnitMeasureCode",
        "Weight",
        "DaysToManufacture",
        "ProductLine",
        "Class",
        "Style",
        "ProductSubcategoryID",
        "ProductModelID",
        "SellStartDate",
        "SellEndDate",
        "DiscontinuedDate",
        "ModifiedDate"
    )
VALUES
    (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?
    )';

    rp := ',(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';


    v_cur := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@LocalSQLSrv2019;
    DBMS_HS_PASSTHROUGH.PARSE@LocalSQLSrv2019(v_cur, v1_sql || rp || rp || rp || rp);
	
	start_time:=SYSTIMESTAMP;

    OPEN C;
	LOOP
		FETCH c INTO a_row;
		EXIT WHEN c%NOTFOUND;
		
		FETCH c INTO b_row;
		if c%NOTFOUND then
            prc:=1;
		ELSE
			FETCH c INTO c_row;
			if c%NOTFOUND then
				prc:=2;
			ELSE
				FETCH c INTO d_row;
				if c%NOTFOUND then
					prc:=3;
				ELSE
					FETCH c INTO e_row;
					if c%NOTFOUND then
						prc:=4;
					ELSE
						prc:=5;
					END IF;
				END IF;
			END IF;
        END IF;

		if prc<5 THEN
		
			---- Deal with the last block of rows 
			DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@LocalSQLSrv2019(v_cur);
			commit;
			
			CASE 
				WHEN prc=1 THEN f_sql:=v1_sql;
				WHEN prc=2 THEN f_sql:=v1_sql || rp;
				WHEN prc=3 THEN f_sql:=v1_sql || rp || rp;
				WHEN prc=4 THEN f_sql:=v1_sql || rp || rp || rp;
			END CASE;
			
			v_cur := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@LocalSQLSrv2019;
			DBMS_HS_PASSTHROUGH.PARSE@LocalSQLSrv2019(v_cur, v1_sql);
		
		END IF;
	
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 1, a_row."Name");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 2, a_row."ProductNumber");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 3, a_row."MakeFlag");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 4, a_row."FinishedGoodsFlag");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 5, a_row."Color");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 6, a_row."SafetyStockLevel");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 7, a_row."ReorderPoint");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 8, a_row."StandardCost");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 9, a_row."ListPrice");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 10, a_row."Size");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 11, a_row."SizeUnitMeasureCode");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 12, a_row."WeightUnitMeasureCode");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 13, a_row."Weight");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 14, a_row."DaysToManufacture");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 15, a_row."ProductLine");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 16, a_row."Class");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 17, a_row."Style");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 18, a_row."ProductSubcategoryID");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 19, a_row."ProductModelID");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 20, a_row."SellStartDate");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 21, a_row."SellEndDate");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 22, a_row."DiscontinuedDate");
		DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23, a_row."ModifiedDate");

		if prc>=2 THEN
		
			--- Bind the 2nd row of data - the 23 below is the bound parameter offset from the previous row of data

			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+1, b_row."Name");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+2, b_row."ProductNumber");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+3, b_row."MakeFlag");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+4, b_row."FinishedGoodsFlag");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+5, b_row."Color");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+6, b_row."SafetyStockLevel");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+7, b_row."ReorderPoint");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+8, b_row."StandardCost");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+9, b_row."ListPrice");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+10, b_row."Size");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+11, b_row."SizeUnitMeasureCode");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+12, b_row."WeightUnitMeasureCode");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+13, b_row."Weight");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+14, b_row."DaysToManufacture");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+15, b_row."ProductLine");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+16, b_row."Class");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+17, b_row."Style");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+18, b_row."ProductSubcategoryID");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+19, b_row."ProductModelID");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+20, b_row."SellStartDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+21, b_row."SellEndDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+22, b_row."DiscontinuedDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 23+23, b_row."ModifiedDate");
		
		END IF;

		if prc>=3 THEN
		
			--- 3rd row of data
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+1, c_row."Name");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+2, c_row."ProductNumber");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+3, c_row."MakeFlag");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+4, c_row."FinishedGoodsFlag");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+5, c_row."Color");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+6, c_row."SafetyStockLevel");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+7, c_row."ReorderPoint");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+8, c_row."StandardCost");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+9, c_row."ListPrice");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+10, c_row."Size");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+11, c_row."SizeUnitMeasureCode");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+12, c_row."WeightUnitMeasureCode");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+13, c_row."Weight");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+14, c_row."DaysToManufacture");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+15, c_row."ProductLine");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+16, c_row."Class");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+17, c_row."Style");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+18, c_row."ProductSubcategoryID");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+19, c_row."ProductModelID");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+20, c_row."SellStartDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+21, c_row."SellEndDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+22, c_row."DiscontinuedDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 46+23, c_row."ModifiedDate");

		END IF;
		
		if prc>=4 THEN
		
			--- 4th row of data
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+1, d_row."Name");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+2, d_row."ProductNumber");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+3, d_row."MakeFlag");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+4, d_row."FinishedGoodsFlag");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+5, d_row."Color");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+6, d_row."SafetyStockLevel");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+7, d_row."ReorderPoint");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+8, d_row."StandardCost");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+9, d_row."ListPrice");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+10, d_row."Size");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+11, d_row."SizeUnitMeasureCode");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+12, d_row."WeightUnitMeasureCode");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+13, d_row."Weight");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+14, d_row."DaysToManufacture");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+15, d_row."ProductLine");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+16, d_row."Class");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+17, d_row."Style");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+18, d_row."ProductSubcategoryID");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+19, d_row."ProductModelID");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+20, d_row."SellStartDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+21, d_row."SellEndDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+22, d_row."DiscontinuedDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 69+23, d_row."ModifiedDate");

		END IF;
		if prc=5 THEN
		
			--- 5th row of data
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+1, e_row."Name");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+2, e_row."ProductNumber");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+3, e_row."MakeFlag");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+4, e_row."FinishedGoodsFlag");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+5, e_row."Color");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+6, e_row."SafetyStockLevel");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+7, e_row."ReorderPoint");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+8, e_row."StandardCost");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+9, e_row."ListPrice");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+10, e_row."Size");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+11, e_row."SizeUnitMeasureCode");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+12, e_row."WeightUnitMeasureCode");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+13, e_row."Weight");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+14, e_row."DaysToManufacture");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+15, e_row."ProductLine");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+16, e_row."Class");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+17, e_row."Style");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+18, e_row."ProductSubcategoryID");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+19, e_row."ProductModelID");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+20, e_row."SellStartDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+21, e_row."SellEndDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+22, e_row."DiscontinuedDate");
			DBMS_HS_PASSTHROUGH.BIND_VARIABLE@LocalSQLSrv2019(v_cur, 92+23, e_row."ModifiedDate");

		END IF;

		rows_affected := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@LocalSQLSrv2019(v_cur);
		

	END LOOP;
    CLOSE C;
	DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@LocalSQLSrv2019(v_cur);
	
	Commit;
	
	end_time:=SYSTIMESTAMP;

	SELECT 
	EXTRACT(DAY FROM (end_time - start_time)) * 86400 +
	EXTRACT(HOUR FROM (end_time - start_time)) * 3600 +
	EXTRACT(MINUTE FROM (end_time - start_time)) * 60 +
	EXTRACT(SECOND FROM (end_time - start_time))
	INTO dur_sec
	FROM dual;
	
	DBMS_OUTPUT.PUT_LINE('Duration : ' || dur_sec);

END;

The implementation involves complex parameter binding logic to handle 1-5 rows per execution, with careful cursor management for the final partial batch. Key considerations:

SQL Server limit: Maximum 1,000 rows per INSERT statement.
Oracle limit: Recommended maximum of 1,000 bound parameters to avoid ORA-28553.
Performance trade-off: Increased code complexity vs. 30%+ performance improvement.

Performance: Over 30% faster than single-row method.

Method 4: SPOOL + BCP (fastest)

The highest-performance method combines Oracle's SPOOL command with Easysoft's BCP tool.

Step 1: Configure Oracle session and export data

SET TERMOUT OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET HEADING OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

SPOOL /tmp/products.csv

SELECT 
    "Name" || '|' || "ProductNumber" || '|' || "MakeFlag" || '|' ||
    "FinishedGoodsFlag" || '|' || "Color" || '|' || "SafetyStockLevel" || '|' ||
    "ReorderPoint" || '|' || "StandardCost" || '|' || "ListPrice" || '|' ||
    "Size" || '|' || "SizeUnitMeasureCode" || '|' || "WeightUnitMeasureCode" || '|' ||
    "Weight" || '|' || "DaysToManufacture" || '|' || "ProductLine" || '|' ||
    "Class" || '|' || "Style" || '|' || "ProductSubcategoryID" || '|' ||
    "ProductModelID" || '|' || "SellStartDate" || '|' || "SellEndDate" || '|' ||
    "DiscontinuedDate" || '|' || "ModifiedDate"
FROM PRODUCTS;

SPOOL OFF
QUIT;

Important Choose a field terminator (like '|') that doesn't appear in your text data to avoid parsing issues.

Step 2: Execute the data export

sqlplus -s system/oracle@free @sql.txt

Step 3: Import using BCP

/usr/local/easysoft/sqlserver/bcp/bcp test.dbo.Products IN /tmp/products.csv \
    -S 192.168.6.18\\SQLSrv2019 -U test -P test -t "|" -c

Best Performance: 2 minutes 43 seconds for 2,064,384 rows, This represents the fastest method for large-scale data transfer from Oracle to SQL Server.

Additional resources: For more information about the BCP tool, refer to this tutorial.

About Easysoft
Contact us
About us
Clients
Blog
Careers

Legal
Privacy statement

© 1993 - 2025 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.

Transferring large datasets between Oracle and SQL Server

Test environment

Creating test data in SQL Server

Initial data setup

Preparing for bidirectional testing

Inserting Data into Oracle from SQL Server

Method 1: Direct Oracle database link (baseline)

Method 2: BCP + SQL*Loader (optimized)

Step 1: Extract data using BCP

Step 2: Create SQL*Loader control file

Step 3: Execute the combined process

Cleanup

Inserting data from Oracle to SQL Server

Method 1: Row-by-row `INSERT` (not recommended)

Method 2: `DBMS_HS_PASSTHROUGH` (single row)

Method 3: `DBMS_HS_PASSTHROUGH` (five rows per statement)

Method 4: SPOOL + BCP (fastest)

Step 1: Configure Oracle session and export data

Step 2: Execute the data export

Step 3: Import using BCP

About Easysoft

Products

Services

Licensing

Support

Search

Popular Resources

Legal

Transferring large datasets between Oracle and SQL Server

Test environment

Creating test data in SQL Server

Initial data setup

Preparing for bidirectional testing

Inserting Data into Oracle from SQL Server

Method 1: Direct Oracle database link (baseline)

Method 2: BCP + SQL*Loader (optimized)

Step 1: Extract data using BCP

Step 2: Create SQL*Loader control file

Step 3: Execute the combined process

Cleanup

Inserting data from Oracle to SQL Server

Method 1: Row-by-row INSERT (not recommended)

Method 2: DBMS_HS_PASSTHROUGH (single row)

Method 3: DBMS_HS_PASSTHROUGH (five rows per statement)

Method 4: SPOOL + BCP (fastest)

Step 1: Configure Oracle session and export data

Step 2: Execute the data export

Step 3: Import using BCP

About Easysoft

Products

Services

Licensing

Support

Search

Popular Resources

Legal

Method 1: Row-by-row `INSERT` (not recommended)

Method 2: `DBMS_HS_PASSTHROUGH` (single row)

Method 3: `DBMS_HS_PASSTHROUGH` (five rows per statement)