Hi.

I am processig data by comparing Oracle data with Sql server data (LinkServer to Oracle is not an option). I have the connections in a web.config file and able to query the Oracle database and the Sql Server database.

I would like to load a Sql Server temp table from a Oracle select statement.  What is the best way to do this?

Thanks

If you are able to connect both database then what is the problem.

Select data from oracle database and insert it into sql database.

Other wise you can import table using dsn also.

 Umm.  If this was a simple solution, I think people would have answered it immediately.  I guess it is not possible to do an insert into SQL Server table as select from Oracle table.  My current work around is select from oracle table into a datatable, loop thru the datatable and insert into sql server temp table.  This solution is clunky and slow.  I was hoping for something quicker and easier.

I had inserted oracle table data into sql table using DTS. if you want to do like that then i will provide you the solution.

May be you should use SQL Server Integration Services. Set up a data flow task, get data from Oracle and push it to a SQL Server table. You may not even need to write code, but there may be some data conversions needed which can also be configured in SSIS.

If you are not proficient in the use of SSIS, I highly recommend my book which btw has even an Oracle <-->SQL Server example. This is a beginners book with very little code.

http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book