1

I need to use SQL in an automation to compare the data from the "TemporaryDE" with the Data from the "CompareDe". If the CompanyName value from the "TemporaryDE" is not present in the "CompareDe" - to update the "CompareDe" data extension with the CompanyName values and the CustomerID values, and also to update a 3rd data extension calles "AMPscript" with all the values from all the fields from the "TemporaryDE" data extention !!But only for the ones that were not found in the "CompareDe" data extension!!. Since this is my first time using SQL I am pretty sure that my code is wrong.

    SELECT s.[CustomerID]
      ,s.[CompanyName]
FROM [dbo].[TemporaryDE] s
LEFT JOIN [dbo].[CompareDe] d ON 
    d.[CustomerID] = s.[CustomerID] AND 
    ISNULL(d.[CompanyName],'') = ISNULL(s.[CompanyName],'') AND 
WHERE d.[CustomerID] is NULL
        (SELECT s.[CustomerID]
               ,s.[CompanyName]
               ,s.[ContactName]
               ,s.[ContactTitle]
               ,s.[Address]
               ,s.[City]
               ,s.[Region]
               ,s.[PostalCode]
               ,s.[FirstName]
               ,s.[LastName]
               ,s.[Industry]
               ,s.[Email]
                FROM [dbo].[TemporaryDE] s
                LEFT JOIN [dbo].[AMPscript Function 3 - v3] d ON
                d.[CustomeID] = s.[CustomerID] AND
                d.[CompanyName] = s.[CompanyName] AND
                d.[ContactName] = s.[ContactName] AND
                d.[ContactTitle] = s.[ContactTitle] AND
                d.[Address] = s.[Address] AND
                d.[City] = s.[City] AND
                d.[Region] = s.[Region] AND
                d.[PostalCode] = s.[PostalCode] AND
                d.[Industry] = s.[Industry] AND
                d.[Email] = s.[Email] AND
                ISNULL(d.[CompanyName],'') = ISNULL(s.[CompanyName],'') AND
                WHERE d.[CustomerID] is NULL)
 WHERE d.[CustomerID] is NULL
  • you can't join temporaryDE & CompareDE if the companyID is null/empty on compareDE however if you have companyid as the common key and companyname is missing you can update it. – Finan Nazazi Apr 2 at 8:30
  • Do you know of any way how I can do what I need. Verify if some of the data in the first DE is also in the second, and if not, to copy the missing data in the second DE and also in the third DE? – Calin Bostan Apr 2 at 11:10
0

Try this one as an update/overwrite to populate the third DE and you can do a select * from [thirdDE] to update the second DE

SELECT s.[CustomerID]
      ,s.[CompanyName]
FROM [dbo].[TemporaryDE] s
where s.CustomerID not in ( select d.customerID from [dbo].[CompareDe] d) 
or 
exists (select 'x' from [dbo].[CompareDe] d where s.CustomerID  = d.CustomerID and ISNULL(d.[CompanyName],'') = '')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.