0

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.

enter image description here

1
  • I would suggest looking at query activities
    – EazyE
    Mar 26 at 22:19
0

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.

1
  • Thanks Nishcha. This solution worked perfectly.
    – scottola
    Mar 29 at 2:26
1

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.

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