I am extracting data from an Oracle table into a text file on SQL Server and then 'bcp'ing the text file into a table. It would save me lot of time if I could pipe the result of the query into my bcp program. In other words I dont want to create the text file. As I extract data it should be bcp'd straight into the table instead of going into a text file.
Can you explain more about it. Like how it would take lesser time.
Have you considered the overheads while transfereing directly.
Thanks for the reply. The goal is to extract some data out of an Oracle table and load it into a SQL server table on a daily basis. (1) I tried the package to extract from Oracle table and load it and it took long. (2)Then i tried running the extract SQL in Oracle and creating a file in the Oracle server, FTP the file from Oracle server to SQL Server and then running bcp. This was slightly better than (1) but still not acceptable to user. We have 10 million rows to load every day. (3)To further improve upon it, I installed an Oracle client on the NT server and ran the script from NT server against oracle table and saved the file directly on NT server. This eliminated the task of FTPing the file and saved some time.
Now I want to try running the script and bcp at the same time on NT server. This method will eliminate creation of file - meaning that as I run the script it will fetch the number of bytes from source table and transfer it to the bcp process as an input. We call this piping. I have done piping in oracle and you dont need to create the text file. I would like Data to be extratced and thrown right into the bcp process. Hoep I was able to explain. The question is - is there a piping method available in the SQL Server world _ I am sure there is.
Just go through the DTS tools provided by Sql Server which is used to import and export data from heterogeneous Data source and destination.
All this takes place through OLE-DB Provider.You can schedule your DTS Package to automatically transfer data.You can customised your package into third party product through COM interfaces.DTS support many of the ODBC complaint databases DB2,informix,Oracle.So go through the usages of DTS Tools you will definitly find the solution.