oracle sql and dba tutorial logo


Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step

Oracle SQL

Tutorial for Oracle SQL

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions

Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.

MySQL Tutorial

Learn MySQL step by step










How to Migrate data from MS SQL Server to Oracle using SQL Loader utility

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.

Column Name Data Type
CustomerID varchar(5)
CompanyName varchar(40)
ContactName varchar(30)
ContactTitle varchar(30)
Address varchar(60)
City varchar(15)
Region varchar(15)
PostalCode varchar(10)
Country varchar(15)
Phone varchar(24)
Fax varchar(24)

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

mssql customers table


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

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

bcp command in sql server

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.

tab delimited file

  Next step: transfer the file to Linux machine running Oracle




HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats

Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved