How to Load or copy data from SQL Server or excel to Oracle

If you want any software and automatic conversion to migrate non oracle database to oracle then you can use Oracle SQL Developer Migration Workbench which can be used to migrate Microsoft Access, Microsoft SQL Server, MySQL and Sybase databases to Oracle.

However you wish to load a table sample data from non-oracle to oracle database. You may wish to do the task manually. Below is the manual procedure by which you can load data to oracle. It explain also if you have data in a flat file then how you can can able to load it into your oracle database.

Though at first time it may seem to you a difficult one but in this post I will try to make it easy. The steps involved to copy SQL Server data to an oracle database is given below. However this procedure is also applied if you want to import data from an excel flat file to an oracle database.

Step 1: Export Data to a CSV file:
You have to proceed table by table if you want to copy data from SQL Server database to Oracle.
For each table data you have to export data to a flat file and convert it to a CSV file.

Don't bother with .CSV or name CSV extension. It is nothing just abbreviate form of Comma Separated Values. If you save a normal text file with the comma between the record parts then that file is a CSV file.

You can directly export data in a CSV file from SQL SERVER database or just export data to a flat file and then convert it to a CSV file.

Export data to a flat file is discussed in http://arjudba.blogspot.com/2008/05/how-to-export-data-to-flat-file.html. Now open this file with excel and from excel file you can easily convert to a CSV file. To do it just open the excel file and >click file manu and >select save as. A pop up window will be displayed. Go to Save as Type section and select .CSV extention and click on save button. You now have got the .CSV file and you have finished step 1.

It will be more easily if you can directly export data to a CSV format. Easily you can do by separating column value of the table with an extension.

Like, you want to copy or load emp table from sql server to oracle.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NUMBER
EMP_NAME VARCHAR2(10)
DEPT_NO NUMBER
SQL> select emp_no ||','||emp_name ||','||dept_no from emp;

EMP_NO||','||EMP_NAME||','||DEPT_NO
--------------------------------------------------------------------------------
1,ddd,10
2,aaa,11
3,bbb,10

Save the output to a emp.csv file.

Step 2: Create a Control file:
In this are emp.csv is called datafile where data of the table to be loaded exists. Never mix with datafile of oracle with this emp.csv. This one is SQL*Loader datafile and oracle datafile are of .dbf extension. After successfully creating data file create a control file. Also don't mix this control file with database control file. This control file instructs SQL*loader how to load data.

Here is the control file. In my other posts of my blog I will go detail with it.

LOAD DATA
INFILE '/export/home/oracle/emp.dat'
INTO TABLE emp
FIELDS TERMINATED BY ','
(emp_no CHAR(2), emp_name CHAR(10), dept_no CHAR(2))

Note that whether datatype is number or varchar2 in control file it is specified as CHAR.
I save the control file as emp.ctl


Step3: Go to oracle database and create the emp table.
I created as below.
CREATE TABLE ARJU.EMP
( EMP_NO NUMBER,
EMP_NAME VARCHAR2(10),
DEPT_NO NUMBER
)TABLESPACE USER_TBS;
Table created.
Step 4: Invoke SQL*Loader and load data.
Copy datafile, control file to the oracle database and invoke sqlldr to load data.
$sqlldr arju/a control=/export/home/oracle/emp.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 20 06:21:28 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 3
Let's check logfile if any error.
bash-3.00$ cat emp.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 20 06:21:28 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: /export/home/oracle/emp.ctl
Data File: /export/home/oracle/emp.dat
Bad File: /export/home/oracle/emp.bad
.
.

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0

Step 5: See the data from database and You are done:
SQL> select * from emp;

EMP_NO EMP_NAME DEPT_NO
---------- ---------- ----------
1 ddd 10
2 aaa 11
3 bbb 10