Pulling data from multiple data extensions

Question

I am trying to pull in data from multiple data extensions. I have a sending DE, but other data are stores in separate tables. For example, DE "X" has my subscriber data and has a field for "product code." And I need to pull the product name and price from another table (DE "Y"). Does anyone have any example AMPScript code for that?

Thanks!

Answer 1

Assuming you are sending to DE_X, product_code will be a substitution string.

There are a couple ways to do this - most of it depends on your data, and how long a full lookup of rows might take.

Lookup()

If all you are after is the name and price, you could do two quick lookups.

%%[
    /* Assuming first row only - use for loop for additional rows */
    @price = lookup('DE_Y', 'product_price' ,'product_code', product_code)
    @name = lookup('DE_Y', 'product_name' ,'product_code', product_code)
]%%

Lookup[Ordered]Rows(), Lookup[Ordered]RowsCS()

If there are multiple rows to match on product code, use lookuprows(), or lookuporderedrows(). Using lookuporderedrows() will order them by a specific field (ie price, or date). Note these are not case specific. Adding the cs at the end of the function name will ensure that cases are matched.

This method may be a little better, as the row set is cached, so the functions after the initial look up are more performant.

%%[
    @rowset = LookupRows('DE_Y','product_code', product_code)
    /* Assuming first row only - use for loop for additional rows */
    @price = field(row(@rowset,1), 'product_price')
    @name = field(row(@rowset,1), 'product_name')
]%%

Queries and Performance

If you are pulling from several data extensions, try to get your data as flat as possible prior to sending. A lookup or two to one data extension won't hinder your send speeds much, but the more complexity you add to your data, the longer a send can take.

NOTE: The impact to each email being sent is in microseconds - so if your sends are in the thousands, you wouldn't see much impact. If your sends are in the hundred thousands, things get much slower due to the scale of the send.

Answer 2

All you need to do is utilize the lookuprows() or lookuporderedrows() AMPScript functions.

There are several examples here on my blog.

You can nest them as much as you want, however, sometimes it's a better solution to consolidate your external data into a single data extension (using Query Activities) before you send.