Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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...

share|improve this question

1 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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