best approach to transfer large volume of data(spread across 3 tables) from one company(oracle) to another company(sqlserver)

our company is running in oracle server. the data which need to provide to another company is running sqlserver.

the data which need to provide to another company is around 85 million rows. each row will have a clob field that will be around 10-15 k.

what is the best way to send them the data?

We have following options: a. we can export the dump using oracle's expdp. Provide the dump to another company. They will first import data into oracle server. And then use sqlserver's link server to import data from oracle server into sqlserver.

b. create the text files in xml format and send it across to another company. They will directly import it into sqlserver.

it is one time effort. for the given volume, what is your recommendation? what is the best approach we can take? what are the pros/cons?

Please tell us.

Thanks in Advance.
more ▼

asked Jun 29, 2010 at 01:17 AM in Default

bhaskar 1 gravatar image

bhaskar 1
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

I'm not an Oracle DBA but I work with moving data in and out of Oracle from SQL Server quite a lot. In general, each product is pretty good at pulling data from the other product and both products stink at moving data into the other product. Generally I've found that OPENQUERY works best from SQL Server to pull the data from Oracle. You have to build a dynamic query, but that's not a big deal. Pass as many parameters through the OPENQUERY as you can to limit the data coming back. On the Oracle side, they use the Transparent Gateway (a poorly named product) to pull the data from SQL Server. It works well.

If you want to spend a little money, there are third party products available that work with SSIS to provide a data source and destination to Oracle. I've seen these work and they're extremely fast. You can do a Boogle search to track them down.

Finally, if you can't directly connect the two servers, then bcp in/out of SQL Server and whatever the Oracle equivalent is, would be the best way to go.

In every case, it's always best to filter the data as close to the data as you can. This means passing the parameters to Oracle if you're reading from SQL Server. Otherwise, you move a lot of unnecessary data and it will affect the overall performance of the system pretty radically.
more ▼

answered Jun 29, 2010 at 04:21 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left

I have no experience of Oracle databases so cannot talk from experience and you may find that most people on this forum are dedicated SQL Server DBAs so you might have to ask the question elsewhere to get a range of experienced opinions.

For my preference I would go for the linked server approach as that saves the hop in the middle of getting anything into csv or xml or whatever.

Microsoft have a lot to say about this as they want to win SQL Server customers so take a look at their recommendations here http://www.microsoft.com/sqlserver/2005/en/us/migration-oracle.aspx
more ▼

answered Jun 29, 2010 at 02:33 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

test comment
Jun 29, 2010 at 03:07 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

I have no experience with Oracle, however I would look at doing it as follows:

  1. Export table as per your suggestion
  2. Import table....
  3. Use SSIS to pump the data from Oracle to SQL Server (running the pump on the target server to reduce network traffic overheads)
more ▼

answered Jun 29, 2010 at 02:53 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Plain and simple... Tab separated files using Oracle's SQLExport and SQL Server's Bulk Insert.
more ▼

answered Jun 30, 2010 at 03:30 PM

Jeff Moden gravatar image

Jeff Moden
1.8k 3 4 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 29, 2010 at 01:17 AM

Seen: 3277 times

Last Updated: Jun 29, 2010 at 01:17 AM