Need to Transfer Data From Oracle to SQL Server

[Guest]

Am running CF7 on SQL Server.  I need to extract data from an Oracle server (50K records per month), and also data from a DB2 server.  Have all the connections set, and have tried QofQ, but it only runs on a small number of records.

So am now trying to "pull" the data from Oracle (using a query), and then INSERT that into SQL Server.  Will do the same for the DB2 data.  Then have all the data stored in one place, SQL Server, and run the queries from there.

You can't do an INSERT with QofQ?  I've tried, didn't work.  Don't think QoQ is the answer.  Does anyone have an example, or suggestions of how to query data from other servers, and INSERT that data into your local CF/SQL Server?  I don't think <CFOUTPUT> allows you to INSERT records into a table?

I've experimented with DTS, but not having much luck.  Thanks for any help/advice.

Gary.

[JR__Bob__Dobbs-qSBHQ2]

Look into setting up a linked servers, pointing to your Oracle and DB2 servers, on your MS SQL Server instance.  You then query the Oracle and DB2 data using your SQL Server.

http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

[Guest]

Thanks for the info.  I wasn't aware of creating "linked servers."  I haven't tried that yet.  But learned the hard way that QofQ is limited in the # of records it can handle.  Even my CF7 manual says that about 1,000 records is it's max.  I'm dealing with a million records per 3-day period.

So I simply extracted and converted the Oracle data, and imported to a new SQL Server table.  Then extracted/converted the IBM/DB2 data, and imported into a second, new SQL Server table.  All "joined" fields were made the same formats and lengths.  Then I was able to run a single query with a JOIN, that worked just fine, and ran quite fast given the millions of records it was analyzing, as well as sorting on 4 fields.

But I will research "linked servers", as my company has data in multiple servers (Oracle, SQL Server, DB2) all over the place, and none of the data contains "all the pieces", and similar data (like flight number, customer name) are stored in different formats.  For now, althought I hate duplicating data, it's nice to have it all in one place, in the same format.

I wish users and DB designers would think more about reporting, BEFORE they came up with some of these big initiatives.  Us programmers and DBAs are left to deal with scattered, differently formatted data, to create the web reports they want AFTER new policies, procedures and initiatives are started.  Oh well.  Had to put my 2 cents in.

Many thanks.  Look forward to learning about "linked servers."  Am curious, do you know if there any advantage to "web services" in cases where data is needed from different internal (corporate) or even external (vendor) servers?  I haven't used web services, just seen a demo of it several years ago. Looked promising.  Thanks.

Gary

[JR__Bob__Dobbs-qSBHQ2]

For transfering data between database servers within your network a linked server is probably the way to go.  For transferring data to or from servers on another organization's network web services are an option, but with some limitations.  SOAP web services add an XML wrapper around the data they send.  For a small amount of data this is not a problem, but for a data set of millions of records this becomes a performance issue.  If your data is flat consider using a tab delimited text file instead of XML.

[JR__Bob__Dobbs-qSBHQ2]

Another option for importing data into SQL Server 2005 or higher is Integration Services.

http://www.microsoft.com/sqlserver/2008/en/us/integration.aspx