Compare 2 databases where tables in each database share the same column data

Question

I'm using 2 databases [Elg] and [VTrader]

Within [Elg] there are 3 tables( ElgFileQueue, RXFileQueue, and CostFileQueue) and each shares 2 columns called FileName and FileCatalogedOn. The FileName data is also shared in [VTrader] under the column name OrigName. I'm trying to find every file that comes in through the 3 ELG tables under FileName and compare that vs OrigName data on VTrader. The output should display FileNames and the FileCatalogedOn date that are NOT shared on VTrader OrigName.

Notes: FileCataloguedOn is using datetime I’ve not made very much progress and have been held up on trying to join all 3 tables in Elg and pull the files names into one place and then my idea was to use an outer join to see what files VTrader is missing that Elg contained.

CODE Example

Use Elg
Select 
    FileName
   ,FileCatalogedOn
From 
    ElgIFileQueue
where 
    cast(filecatalogedon as date) = cast(getdate()-1 as date)
order by filecatalogedon desc

This will output yesterdays Filenames from Elg.ElgFileQueue

select 
    OrigName,
    startDT
from 
    VTraderer
where 
    cast(StartDt as date) = cast(getdate()-1 as date) and 
order by startdt desc

This outputs yesterdays files from VTrader and gives me the file names and startDT = file catalogue date.

The goal is to get all files from Elg Tables mentioned and the VTrader table and output any files that are missing from Vtrader that are in Elg.

please provide a [mre] i text form see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — nbk, Sep 12 '22 at 00:17
Please provide the schema of the tables, some example data and the desired output. This will help clarify your requirement. — Chris Schaller, Sep 12 '22 at 00:46

Chris Schaller · Answer 1 · 2022-09-12T02:55:54.373

From your description we can assume that the join criteria is to include both of FileName and the FileCatalogedOn date columns.

We can join between tables in different databases using the 3 part fully qualified name:
[{Database}].[{Schema}].[{Table}] as long as you have adequate permissions and the databases are on the same server. If you are operating in Azure or other hosted SQL vendors, you may need to CREATE EXTERNAL TABLE (Transact-SQL) definitions first.

We can also omit the schema element if the table is in the default schema for the executing user:
[{Database}]..[{Table}]

From there the query follows all the guidance specified here: How to select all records from one table that do not exist in another table?

If you want to return all the data from both tables in the result set for the given date range then OUTER JOIN is a good choice:

SELECT 
    FQ.FileName
   ,FQ.FileCatalogedOn
   ,VT.OrigName
   ,VT.startDT
FROM [Elg]..ElgIFileQueue FQ
LEFT OUTER JOIN [VTrader]..VTraderer VT ON FQ.FileName = VT.OrigName 
                                       AND FQ.FileCatalogedOn = VT.startDT
WHERE
    CAST(FQ.FileCatalogedOn as Date) = CAST(GETDATE()-1 as Date)
ORDER BY FQ.FileCatalogedOn DESC

NOTE: It is not important to also filter the VTraderer table because the filtered column is in the JOIN criteria

Although CAST as Date is SARGable, I still recommend that you pre-parse the filter criteria into discrete values that match the Data Type of the column being filtered. There is a discussion here: Cast to date is sargable but is it a good idea? As an example implementation, in this query I have extracted out the boundaries via SQL, if you are calling this from an application you might evaluate the values in your native code space and simply pass them through as parameters.

DECLARE @DateFrom DateTime = (SELECT CAST(GetDate() as Date))
DECLARE @DateTo DateTime = (SELECT DATEADD(DAY, 1, @DateFrom))

SELECT 
    FQ.FileName
   ,FQ.FileCatalogedOn
   ,VT.OrigName
   ,VT.startDT
FROM [Elg]..ElgIFileQueue FQ
LEFT OUTER JOIN [VTrader]..VTraderer VT ON FQ.FileName = VT.OrigName 
                                       AND FQ.FileCatalogedOn = VT.startDT
WHERE FQ.FileCatalogedOn >= @DateFrom
  AND FQ.FileCatalogedOn < @DateTo

ORDER BY FQ.FileCatalogedOn DESC

NOTE: I am assuming that the columns are DateTime adjust this to your schema as needed

If you only wanted to list the missing files then using a WHERE NOT EXISTS can offer better performance but also makes the intent a lot clearer:

DECLARE @DateFrom DateTime = (SELECT CAST(GetDate() as Date))
DECLARE @DateTo DateTime = (SELECT DATEADD(DAY, 1, @DateFrom))

SELECT 
    FQ.FileName
   ,FQ.FileCatalogedOn
FROM [Elg]..ElgIFileQueue FQ
WHERE FQ.FileCatalogedOn >= @DateFrom
  AND FQ.FileCatalogedOn < @DateTo
  AND NOT EXISTS (
        SELECT 1
        FROM [VTrader]..VTraderer VT 
        WHERE VT.OrigName = FQ.FileName
          AND VT.startDT = FQ.FileCatalogedOn
      )
ORDER BY FQ.FileCatalogedOn DESC

Compare 2 databases where tables in each database share the same column data

1 Answers1