Exclude data if found in another table

[terry_stjean]

I have a process which is pretty basic, reads Order data from a table in a non SQL Server DB and loads to a SQL server Order table.
I need to change the process so it will only loads the Order data from the non SQL Server DB if the main source if the Order is not found in the SQL Server Order table.
Any suggestions on how to change the process.

[t1701]

I think I figured it out.

[MFGF]

You could perhaps define a lookup on the existing SQL Server Order table, with an ID based on the order number, and a second attribute called "Found" populated by a literal value of 'Y'. Set your build to validate the order numbers from the non-SQL Server table against the lookup, and in the properties of the Order Number build element, check the "Accept unmatched member identifiers" option. Add a Transformation Model derivation to pick up the value of the Found attribute from the lookup (which will either be 'Y' or null, depending on whether the Order Number was there). FInally, add a delivery filter to your build to deliver only rows without 'Y' in the derivation.

Cheers!

MF.

[wyconian]

I'd use an except clause in the data stream query, something like

select c1,
         c2,
         c3
from table 1
execpt
select c1,
         c2,
         c3
from table 2

To do that you need to have the same number of columns and data types in the 2 halves of the query