SourceCodes are fields we assign to each email. They correspond to the email name. I need to retrieve the SourceCodes from Data Extension One (our source DE used for sending) and append them to the SourceCode column in Data Extension Two (our tracking DE). What is the simplest way to accomplish this? Can someone help me out with writing a query? Thanks.
One of the many ways to achieve this is by using Ampscipt. Assuming you want to retrieve and store this data while sending the email only. Below is the code -
%%[ set @emailName = EmailName set @sourceCode = SourceCode UpdateDE('Data Extension Two',1,'EmailName',@emailName,'SourceCode',@sourceCode) ]%%
This script will go into your email message and will fetch the "EmailName" and "SourceCode" from your Sendable DE i.e. "Data Extension 1" corresponding to a particular record selected at the send time and will store these values in your targeted data extension i.e. "Data Extension Two".
Note - UpdateDE function will only work in email context and will be executed at send time only.
As you want to use a query for that, you can do it with a simple join:
SELECT de2.EmailName , de2.TotalSent , de2.UniqueClicks , de2.UniqueOpens , de1.SourceCode FROM DataExtension2 AS de2 LEFT JOIN DataExtension1 AS de1 ON de1.EmailName = de2.EmailName
However, I wouldn't recommend doing it that way, as it would require outputting to a Data Extension that is used for the query (both reading and writing to the same table).
Instead, you could do the same logic in the query that builds the DataExtension2 or use an intermediate Data Extension.
Also, this is assuming that EmailName in both tables is unique. If in the DataExtension1 there might be multiple rows for the same Email Name with different SourceCodes, then indeed using AMPScript or SSJS (be it in the email on the send time or in Script Activity after it) a better way.