How AMPscript Update, Insert, and Delete Calls are Processed During Sends

Scenario

Northern Trail Outfitters uses data extensions to manage the subscriber information used for their email sends, and those sends include calls to update records with new or different subscriber information. The person handling these records writes some AMPscript to check and see if the data extension containing those records has been updated using an INSERTDE() call if the record doesn't yet exist. However, at the end of the process, that person receives an error telling them the application cannot insert a duplicate value for the primary key in the data extension.

How the Application Handles AMPscript Calls

Instead of making several different AMPscript calls as part of an email send, the ExactTarget application takes all applicable AMPscript calls and completes them in one call at the end of the send. In the above scenario, the AMPscript call using the INSERTDE() function comes after the row has been added as part of the email send, and thus the ExactTarget errors out that call because the row already exists.

In this case, an UPSERT() AMPscript call handles the check on the existence of the row and adds any additional or necessary information. When writing your AMPscript, always remember that all calls will be processed at the end of the subscriber batch when performing email sends.

The sample code below shows how to correctly check rows for information after a send and update all appropriate fields:

%%[ 
/* DO NOT EDIT */ 
Var @jid, @pnum, @ctype, @ptype, @stype, @pcode 
Var @Hello 
Set @jid = jobid 
/* Set Variables here for easy editing */ 
Set @pnum = "SendTest" 
Set @ctype = "SendTest" 
Set @ptype = "SendTest" 
Set @stype = "SendTest" 
Set @pcode = "SendTest" 
/* Check to see if a record exists with this JobID */ 
Set @lookUp = LookupRows("Campaign Log","JobID",@jid) 
IF RowCount(@lookUp) == 0 THEN 
Set @Hello = 'Hello?' 
/* If no record exists with this JobID Insert Variables into the Data Extension */ 
UPSERTDE("Campaign Log", 1, "JobID", @jid, "ProjectNumber", @pnum, "ComType", @ctype, "ProgType", @ptype, "ProgCode", @pcode, "ProgSubType", @stype) 
ELSE 
Set @Hello = 'Hola!' 
ENDIF 
]%% 
<h1>Variables</h1> 
%%=v(@jid)=%% 
<br/> 
%%=v(@Hello)=%% 
<br/> 
%%=v(@lookUp)=%% 
<br/> 
%%=EMPTY(@lookUp)=%% 
<br/> 
%%=RowCount(@lookUp)=%% 
<br/> 
%%=v(@pnum)=%% 
<br/> 
%%=v(@ctype)=%% 
<br/> 
%%=v(@ptype)=%% 
<br/> 
%%=v(@stype)=%% 
<br/> 
%%=v(@pcode )=%% 
<br/>