Export Query Results from Oracle to SQL Server

Question

I'm trying to export the results from a query in SQL Developer into SQL Server. I'm sure I've done it once before, though my route may well have been convoluted - I can't remember. I may have used a '2-hop' solution (i.e. export to XML or CSV or other intermediary format) in the past, I'm not sure.

The query returns around 5000 rows, and although I may want to repeat this exercise occasionally at some point in the future, I don't mind a 'quick & dirty' solution.

I can easily generate Insert statements for a table, but I can't see a similar way of doing it for a multi-table query.

I did start to look at an Integration Service project and SQL Server Migration Assistant for Oracle, but fell at the first hurdle in each - which I presuming is because I don't have the necessary software (either a full Oracle Client or OO4O). I probably regard these solutions as overkill for my current needs - but I'm willing to be persuaded otherwise.

The oracle version is 10g and my sql server version is 2008 SP1 (running on Windows 2008 x64).

Thanks in advance...

Answer 1

You should look at creating a linked server in SQL Server pointing to Oracle and use OPENQUERY to fetch the results directly in SQL Server.

Something like below.

EXEC sp_addlinkedserver 'OracleSvr', 'Oracle 7.3', 'MSDAORA', 'ORCLDB'; GO SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles'); GO

The below articles will walk thru the steps for setting up the linked server. http://support.microsoft.com/kb/280106 http://technet.microsoft.com/en-us/library/ms188427.aspx