Here is a step by step example on how to copy the data from an MS SQL Server table to Oracle using Oracle's SQL Loader utility.

To describe the scenario, let's say suppose we have a table in MS SQL Server by name "Customers" in Northwind sample database. The SQL Server is running under Windows O/s and Oracle database is running under Linux.

Here is the structure of the customers table in MS SQL Server.

This table has some 91 rows and we need to migrate all these rows to a table in Oracle.

To broadly describes the steps we need to perform to achieve this objective, we need to first convert the MS SQL data from this table into TAB delimited text file and then copy this file to Linux machine running Oracle. We then need to create a table structure in Oracle database with the same structure as in MS SQL by taking appropriate data types and widths. After this we need to write a SQL Loader control file and then run SQL Loader command.

So let's begin.

Step 1.

In the machine running MS SQL Server create a tab delimited to file. To create this file you can use SQL Server's BCP utility or MS SQL Server's DTS package. In our case we will use BCP command.

The MS SQL Server's Bulk Copy Program (BCP) is used to import and export large volumes of data from an MS SQL Server instance. It can be used to export individual tables as well as customized queries.

For more information about BCP utility please visit this Microsoft Documentation

To use BCP to export the data,  run the command line prompt by clicking on Window's Start button and typing cmd in run command

At the Command Prompt type the BCP command with the following arguments

bcp northwind.dbo.customers out customers.txt -c -T -S localhost\sqlexpress

  The arguments which we have used

• out: specifies the data should be exported
• -c : export as ASCII with a tab delimiter and carriage return/line feed line terminator
• -T : Trusted Connection i.e. use Windows authentication
• -S : server to connect to. In our case, we are connecting to local SQL Express instance

Once you type the above command you will get the following output as shown in the pic below

As you can see from the above screenshot, the bcp command has copied all the data into tab delimited file customers.txt

You can open and see the contents in it by opening notepad, here is the screenshot of the just created tab delimited file.

  Next step: transfer the file to Linux machine running Oracle