Sql-server – the fastest method to migrate large tables from Oracle to SQL Server

migrationoraclesql server

We have a table with 169 million rows in a Oracle 10g database, we need to move this table to a MS SQL Server 2012 database.

Describe on the table:

aaa NOT NULL NUMBER(38)
bbb NOT NULL NUMBER(38)
ccc NOT NULL DATE
ddd NOT NULL VARCHAR2(8)
eee NOT NULL DATE
fff NOT NULL NUMBER(3)
ggg NUMBER(38)
hhh DATE
iii VARCHAR2(255)
jjj VARCHAR2(255)
kkk VARCHAR2(4000)

We have tried SSMA (SQL Server Migration Assistant) – takes over 300 hours.
We have tried SSIS (SQL Server Integration Services)- takes over 52 hours.
These options are not practical

I was wondering if 'bcp' is a good option, if yes, what is the syntax for such a data pull? Can we directly pull the data from the Oracle database?

Best Answer

BCP would be a good choice. Export your data to a text file and then import it. To do this follow these steps:

  • Create your new table in sql server

  • Insert 1 row of data into this new table

  • Use BCP to get an export from this table containing this 1 row This is necessary so that you can create an export from Oracle that matches this. Example:

    bcp yourdatabase.dbo.yourtable out yourtable.dat -T -c

  • Check the format in yourtable.dat

  • Generate an output file from Oracle that matches the format of yourtable.dat

  • Use bcp again to load the data file that was created by oracle into sql server. Example:

    bcp yourdatabase.dbo.yourtable in yourtable.dat -T -c

Try with 5000 rows. Get it working properly then time it and load. BCP will be fast.

Related Question