Moving data from an Oracle database to a SQL Server database

mlhapka · Post by **mlhapka** » Tue Apr 20, 2004 2:52 pm

I need to speed up data transfer from an Oracle database to a SQL Server database using an ODBC connection. I wanted to use 2 jobs where the first job transfers the Oracle data to a sequential file. This job is very fast. The problem I am having is getting the sequential file transferred to the SQL Server database. I understand that I can't use the BCP stage when DataStage is on a Unix server. Any suggestions? Also wondering what the fastest way is to truncate that SQL Server table before I insert the data? Thanks

wdudek · Post by **wdudek** » Tue Apr 20, 2004 3:58 pm

I'm not sure about wether or not you could do this on sql server, but loading into oracle, we created files in the format needed by the Oracle bulk loader and then created a stored procedure in oracle that executed the bulk loader as a process on the oracle server. This runs extremely fast, about 35 million rows in 15 minutes. Being able to do this would require that A. you can write a stored procedure in sql server that can either start a dts transaction, or execute bcp. B. Datastage can execute stored procedures through the odbc stage. hope this helps.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 20, 2004 4:24 pm

The same can be done with SQL Server. Use a Sequential File stage (or even an FTP stage) to produce the data file, then (remotely?) trigger a BAT file to perform the BCP command on the Windows machine.