I have 3 data extensions: Master, Local and additional, supporting one which essentially functions as historical Storage of all the records that ever landed in Local. So the data flows like this Master > Local > Storage
Now, I want to have a query which will transfer data from Master to Local but only if such record doesn't already exist in Record Storage. I am using overwrite function as neither Local nor Storage have a Primary Key.
To determine whether a record exists in Storage I check two different fields and only if such combination doesn't already exist in Storage, I move the record to Local.
Everything works as intended until the moment when in Storage appears a record where one of these two fields is empty. From that point on the query below doesn't work anymore and always brings 0 records from Master to Local. Any idea how to get rid of the problem?
SELECT * FROM [Master] WHERE ([Task:Description] +''+ [Task:What:Case:Id]) NOT IN (SELECT ([Task:Description] +''+ [Task:What:Case:Id]) FROM [Storage])