Creating a table in oracle using sql server table

Question

I have a table in SQL Server 2008 R2. It contains 1M or more records. Now I want to create a table in oracle with the same content that is in SQL Server 2008 R2.

Answer 1

There are several ways of doing that. You can first look on the following tutorial: Migrating a Microsoft SQL Server Database to Oracle Database 11g

I have done this task in the past using the following steps:

1. Create the table in the Oracle database (only schema, not data).
2. Export the data from the SQL server to 1 or more CSV (or any other delimiter files (I suggest to create files with no more than 100,000 records)
3. Use SQL*Loader (An oracle utilily) to load the data from the files to the oracle.

The Oracle SQL*Loader utility is a command line tool that allows you to load data from files to Oracle. It uses control file that specifies the source file, its structure and the loading strategy.

The advantage of using the tool vs. loading using INSERT statements is the speed of loading. Since this tool bypass the log files it is extreamly fase.

Here is the link to the SQL Loader tutorial: SQL*Loader FAQ

From this tutorial:

Usage:

 sqlldr username/password@server control=loader.ctl

Control file sample:

(1) load data
(2)    infile 'c:\data\mydata.csv'
(3)   into table emp
(4)    fields terminated by "," optionally enclosed by '"'        
(5)    ( empno, empname, sal, deptno )

Line 1: Speciefies that you want to load data into the table

Line 2: Specifies the source file that contains the data

Line 3: Specifies the destination table

Line 4: Specifies the columns delimiter (Comma in the example) and that string values might be enclosed by " char.

Line 5: Specifies the order of columns in the file

Data files sample (Corresponds to the control file above):

10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20

Hope it helped.

Koby