2

I have a query in marketing cloud that run daily to add data in a data extension.

What should be the "UPDATE TYPE" if i want to add new records in my target data extension.

I do not want to update or overwrite.

Should i select APPEND? what exactly does append do compared to the overwrite and update types?

Update: Yes my question is for Add only but i was just asking about update case in the comments. Just to confirm, the append won't add duplicates correct? There is a chance that my query may encounter records that already exists in my target data extension, and I do not want duplicates. I have the subscriberKey as primary key so I hope query won't error out if it sees any incoming records same as what i have in target DE.

Improve this question

2 Answers 2

Reset to default
5

EDIT

Based on the comments below, this may be your best answer:

SELECT *
FROM SourceDE s
LEFT JOIN DestinationDE d
ON s.PrimaryKey = d.PrimaryKey
WHERE d.PrimaryKey IS NULL

This will force an Update type, but as there will be no duplicates due to the above LeftJoin and Where statement, this will only add to the Destination DE and not update records.

SQL queries in SFMC use the APPEND option to do an add only function.

Append - Adds the query results to the data extension after existing records.

Update - Updates records in the data extension with query results and appends non-matching records.

Overwrite - Delete the data extension's records and add the query results.

(ref from the SFMC help docs)

So, basically you just need to set your type to APPEND and you should be fine with adding to your DE.

Improve this answer
8
  • Ok - so it looks like UPDATE should be a better option that will update the existsing records and if any record does not exists, it will be added to data extension.
    – kuml
    Commented Aug 8, 2017 at 13:23
  • Append/Add Only will error if you have records in your query's results that contravene your primary key's constraints (SubscriberKey is PK). Likewise, an Update will fail if you have multiple instances of the same SubscriberKey in your query results. If you just want to add, not update, but you have SubscriberKeys in your source data that may be in your target data extension, use an outer join in your query from the source to the target. For Update queries, you can reference the target DE in your query.
    – Macca
    Commented Aug 8, 2017 at 14:29
  • So, I am using this query to copy data from my source data extension to target data extension. Select s.SubscriberKey, s.DataCategory from [MY SOURCE DE] as s How can I use outer join in this so make sure query does not error out because there will be many cases in which incoming data already exists in my target data extension. Hence, I am looking to only add new records and ignore the existing records.
    – kuml
    Commented Aug 8, 2017 at 14:41
  • 1
    Thank you @Gortonington - I have updated the query something like this SELECT DISTINCT s.SubscriberKey, s.DataCategory FROM SourceDE s LEFT JOIN TARGET-DE d ON s.SubscriberKeyPrimaryKey = d.SubscriberKeyPrimaryKey WHERE d.SubscriberKeyPrimaryKey IS NULL
    – kuml
    Commented Aug 8, 2017 at 17:51
  • 2
    That query should work I believe - also, please delete some of your older or irrelevant comments to prevent this from moving to 'chat'. I have already deleted mine.
    – Gortonington
    Commented Aug 8, 2017 at 18:23
5

These are what the 3 query types are:

Append: Will only add records to the table without affecting the existing records.

Update: Will update existing records if their is a match or will add the record if no match is found.

Overwrite: Will overwrite the whole data extension regardless of what existing data is in the data extension.

Based on what you are doing, you need to consider many reasons why you may choose to append over an update or overwrite. That is purely based on the scenario you are dealing with.

To answer your question though, you will need to set the query to Append as you mention you would only like to add data to the data extension.

More information in regards to queries here

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .