1

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?

|improve this question
  • You can link directly to oracle and pull the data out but it wont be quick. Use Linked Server Oracle drivers, write some code as a stored proc and run from your SQL server environment. Then its down to things like the network, transfer bandwidth etc. – Peter Davies Sep 29 '17 at 10:05
3

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.