dham84 dham84 - 2 years ago 279
SQL Question

SQL query and cross match data from two different database servers using Toad

I've just been assigned a small task at work with SQL and I'm very much outdated on it, I know pretty basic stuff, the task is quite tedious and I'm sure there's an easier way to do it than one by one.

Here's the issue:

Company has two databases in different servers (one is Oracle), I am using Toad for Oracle to query both Databases.

I have an Excel file with a list of 6k receipt numbers that were supposed to have been transferred to Oracle but for some reason, not all of them did, so I need to check each number is on both databases and then check a few other fields like the bill amount. That last part can be ignored for now.

I am more into building a query in SQL or perhaps some formula in excel where I can paste in three columns the data, the result from the first DB and the result from the 2nd and cross check automatically. Oh and almost forgot, some numbers are repeated in all data sources :S

Any help would be much appreciated, hope I explained myself clear enough.

Answer Source

There are several ways to do it. Considering your skill set I would do it like this:

  1. Paste the data into N columns in Excel.
  2. Create an SQL statement on the first row using the data from the N columns. Should look something like this: =concat("select ", A1 , " from dual where not exists (select 1 from receipts_table where receipt_id = '", A1, "');" Add additional where clauses for the other fields you want to check.
  3. Copy/paste the SQL into Toad and check that it runs. It should print the receipt_id if the receipt is missing or doesn't have correct values in Oracle.
  4. Use "Fill->Down" in Excel to create one SQL for every receipt.
  5. Copy the 6k SQL statements to Toad and run them as a script.
  6. Find the lines that produced output. Those are the missing receipts.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download