Inserting SQL Server data with Oracle SQL*Loader

The typical route our customers take when integrating their Oracle and SQL Server data is to use Oracle Heterogeneous Services with our SQL Server ODBC driver. This approach is described in our DG4ODBC tutorial.

An alternative method is to use the bcp tool included in the SQL Server ODBC driver distribution in conjunction with Oracle SQL*Loader. You can use a named pipe as the data conduit between bcp and SQL*Loader. You don't need to use bcp to write the SQL Server data to a physical file and then use this file as the data source for SQL*Loader (although you can if your prefer).

1. Create some sample data in SQL Server:

   $ cd /usr/local/easysoft/unixodbc/bin
   $ ./isql.sh -v SQLSERVER_SAMPLE
   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+
   SQL> create table bcptest (c1 int, c2 varchar(20))
   SQLRowCount returns -1
   SQL> insert into bcptest values (1, 'Hello'),(2,'World')
   SQLRowCount returns 2
   SQL> select * from bcptest
   +------------+---------------------+
   | c1 | c2    |
   +------------+---------------------+
   | 1  | Hello |
   | 2  | World |
   +------------+---------------------+
   SQLRowCount returns -1
   2 rows fetched
   SQL>

2. Create a table in Oracle to hold the SQL Server data:

   SQL> create table bcptest (c1 int, c2 varchar(20))

3. Create a named pipe:

   $ mknod /tmp/bcp-pipe p

4. Create and populate a SQL*Loader control file:

   $ cat /tmp/bcp.ctl
   
   load data
    append
    into table bcptest
    fields terminated by "\t"
    ( c1, c2 )

5. Run SQL*Loader in the background, where it will remain waiting for data to arrive:

   $ cd /u01/app/oracle/product/11.2.0/xe/bin
   $ ./sqlldr myuser/password data=/tmp/bcp-pipe control=/tmp/bcp.ctl &

6. Use bcp to write to the pipe:

   $ cd /usr/local/easysoft/sqlserver/bcp/
   $ ./bcp test.dbo.bcptest out /tmp/bcp-pipe -c -S myserver:1433 -U sa -P password

The SQL*Loader process reads data from the pipe, inserts the records into Oracle and terminates:

$ 
SQL*Loader: Release 11.2.0.2.0 - Production on Fri Nov 4 07:18:53 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

-bash-4.1$ Commit point reached - logical record count 2

[1]+  Done ./sqlldr myuser/mypassword data=/tmp/bcp-pipe control=/tmp/bcp.ctl &

The records are now in Oracle, as shown by the following query in SQL*Plus:

$ ./sqlplus

SQL> select * from bcptest;

        C1 C2
---------- --------------------
         1 Hello
         2 World