0

I have a Staging Data Extension that gets records imported into daily. This import brings in new records and updated records. The caveat is the import will bring in identical records from the previous day. A record will be included for 2 days (identical data) in the case of a failed import.

I need to take the records from the Staging Data Extension into a Master Data Extension. I only want to bring in records that are new (don't exist in Master) or Updated records (records that are in Master but have a different value in a field in the Staging DE). I don't want to bring in records from Staging that are identical to Master.

I wrote a Query using a LEFT JOIN and it appears to work as desired. I'm not a SQL guru so I was hoping to get a second set of eyes on this to see if I'm missing something. There are more fields than the sample below I removed most for brevity. I will also need to compare a Source and Destination field to reset a date field to the current date.

--NEW/UPDATED RECORDS FROM Source TO Destination
SELECT s.[ID]
      ,s.[FirstName]
      ,s.[LastName]
      ,s.[Result]
      ,CASE WHEN ISNULL(s.[Result],'') != ISNULL(d.[Result],'') THEN GETDATE() ELSE NULL END AS [StartDate]
FROM [dbo].[Source] s
LEFT JOIN [dbo].[Destination] d ON 
    d.[ID] = s.[ID] AND 
    ISNULL(d.[FirstName],'') = ISNULL(s.[FirstName],'') AND 
    ISNULL(d.[LastName],'') = ISNULL(s.[LastName],'') AND 
    ISNULL(d.[Result],'') = ISNULL(s.[Result],'')
WHERE d.[ID] IS NULL
  • What makes a row unique? – Adam Spriggs Jan 21 '16 at 23:05
  • The Primary Key is ID. – victorcorey Jan 21 '16 at 23:17
1

Here's another approach. I would go with which ever preforms best.

I don't see the harm in updating master if a value in staging is the same as the master. It is additional/unnecessary work. But, allowing this will make for a similar query design and may actually preform better.

In this approach, no where conditions are needed. Since this is a left join, you can check for new records by testing if m.[ID] is null. However, this case statement isn't necessary either if data extension is configured to use a default value of "Use Current Date". The default value will only be used on row inserts when no value is specified. Updated rows will not be affected by the default value.

SELECT 
 s.[ID]
,s.[FirstName]
,s.[LastName]
,s.[Result]
,m.[FirstName]
,m.[LastName]
,m.[Status]
, case 
    when (m.[ID] is null) then getdate()
    else m.StartDate
  end as StartDate
FROM [Staging] s
LEFT JOIN [Master] m ON (m.[ID] = s.[ID])
0

It seems like you'd only update the Master if there's a matching row in the Staging -- (d.[id] = s.[id]) -- which would never insert new rows from staging. To address that you could write it like this:

SELECT 
s.[ID]
,s.[FirstName]
,s.[LastName]
,s.[Result]
, CASE 
  WHEN ISNULL(s.[Result],'') != ISNULL(m.[Result],'') THEN GETDATE() 
  ELSE NULL 
 END AS [StartDate]
FROM [Staging] s
LEFT JOIN [Master] m ON (m.[ID] = s.[ID])
WHERE 
/* no matching row is found in master */
m.[ID] is NULL
/* or matching rows is found, but column values have changed */
or (
  ISNULL(m.[FirstName],'') != ISNULL(s.[FirstName],'') 
  AND ISNULL(m.[LastName],'') != ISNULL(s.[LastName],'') 
  AND ISNULL(m.[Result],'') != ISNULL(s.[Result],'')
)
  • Hmmm...I do have "WHERE d.[ID] IS NULL" at the end. And d.[ID] = s.[ID] will return a NULL if the Staging record does not match Master, which it wouldn't if it doesn't exist in Master. In testing I have a record in Staging that's not in Master and it does get returned. Am I missing something? – victorcorey Jan 22 '16 at 20:40
  • I think the difference is with the or in my example query -- in the where clause. You're still only returning rows if FirstName, LastName and Result are the same in both Data Extensions. – Adam Spriggs Jan 22 '16 at 21:13

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.