DataFlow WHERE Clause - Check Another Table ?

[#1]

I am doing a simple Dataflow transform from .CSV to SQL table, but sometimes the vendor sends the same file twice, and we are loading duplicate records.

In the dataflow WHERE clause, is the SQL table target "in scope" to check if records already exist ?

I want to add something like

WHERE IMPORTED_ID NOT IN ( Select LOADED_ID FROM EXISTING_TABLE )

If not, what's another way to check if records already exist in the target or another table ?

[sapuserapr2021]

Hi Gabriel,

One solution is to add a Table_Comparison transform into your dataflow. Here is an example:

Notice the CADDOCMASTER_Compare transform. In there you can specify a table to compare to which in your case would be the target table you are populating. You can also specify one or more columns that will provide uniqueness for a key. If you already have a unique key column you would just use that. You also specify compare columns that will be used to detect differences. If a recode comes in that has the same key values, it will update that row assuming that one or more of the compare columns are different. If the key values don't match any record already in the table, a new row is inserted. You can also have the comparison delete records if you want. Here's a picture of the Compare transform in the example above:

I hope this is of some help.

Dave

[dunncrew]

Thank you. I will try that.

[dunncrew]

I have an alpha field as key, so getting an error due to data type. I will have to try something else.

Generated key column <TRACEID> for table <AMGIMP01GD> must be of type REAL, FLOAT, DOUBLE, INTEGER, DECIMAL, or NUMERIC.