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

From $1

    This document contains scenario and conceptual information about how the ExactTarget application processes AMPscript calls contained within emails and landing pages. You can use this information to create AMPscript to properly obtain and process information from your email sends and data extensions.

    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/>
    The sample code above declares the variables to be included in the data extension and checks that data extension for a row with the send's JobID. If the record isn't there, the AMPscript adds the row and the pertinent information. If the row does exist, it updates that row with the information. This accomplishes the task and avoids any problems with errors due to duplicate JobIDs.

     

     


    This page was last updated by Ryan Williams on Tue, 29 Nov 2011 13:34:56 GMT.

    If you require assistance with the ExactTarget application, please contact Global Support. If you wish to send Ryan direct feedback, fill out the form below:

     


    Was This Page Helpful?
    Suggestions or Comments:
    Name (optional):
    Email Address (optional):
    Enter 18330 backwards:
       
    Tags: (Edit tags)
    • No tags
     
    Comments (0)
    You must login to post a comment.

     
    Powered by MindTouch 2010
    Admin