June 22, 2011 at 10:16 am
I need to load several million rows of data from Oracel 8i ot SQL 2008. An preferences for using openquery openrowset, or another solution
June 22, 2011 at 4:12 pm
Have you considered BULK INSERT?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 29, 2011 at 5:42 pm
Bulk insert will work fine for getting the data into SQL Server; the trick is getting the data out of Oracle quickly.
I'd suggest two choices:
The "Connected" Model
Look at Integration Services (SSIS) for an "end-to-end" approach. It can load the data into SQL Server as quickly as bulk insert, but it can also get the data out of Oracle fairly easily and quickly. You will want to run your SSIS package on the server for SQL Server to get the best performance.
This is a good "connected" approach, so only appropriate if both servers are guaranteed to be up at the time you run your import. It does require some work to get it all working, although the Data Import Wizard will get you started fairly quickly. You will also need an Oracle client installed on the server for SQL Server.
The "Disconnected" Model
Look at using the Oracle data pump to get the data out of Oracle into a file, and then bulk insert (or even bcp) in SQL Server to load the data. The nice thing about this is if you are doing it regularly, you can archive the file and recall it if you need to at a later date. Also, you don't have to do the export and import simultaneously.
In my experience, the second approach is easier to get going, but can be a bit slower than the SSIS approach (particularly if you are using something like ftp in the middle to transmit the file between servers.)
June 30, 2011 at 5:01 am
Bruce W Cassidy (6/29/2011)
Bulk insert will work fine for getting the data into SQL Server; the trick is getting the data out of Oracle quickly.The "Disconnected" Model
Look at using the Oracle data pump to get the data out of Oracle into a file, and then bulk insert (or even bcp) in SQL Server to load the data.
Would you mind in posting the script you use to read an Oracle expdp dump file? - other than impdp, of course.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply