Package: Upsert Task

The Upsert task uses the MERGE statement on which Blackbaud bases the pattern for the template packages. For more information about MERGE statements, see Microsoft's MSDN article at MERGE (Transact-SQL). This task inserts new rows and updates existing rows on the BBDW database.

You can adjust the example for other tables. The example uses aliases s and t for the source and target tables respectively. The statement identifies the target up front:

on (t.[INTERACTIONRESPONSESYSTEMID] = s.[INTERACTIONRESPONSESYSTEMID])

The source is identified in the AS clause:

as s

The MERGE statement specifies actions for when there are and are not matches. Matches are determined by the ON clause:

on (t.[INTERACTIONRESPONSESYSTEMID] = s.[INTERACTIONRESPONSESYSTEMID])

The behavior for no match is specified after:

when not matched by target
  then

The behavior for a match is specified after:

when matched
  then

Some possible actions that follow WHEN NOT MATCHED and WHEN MATCHED are included in the following example.

declare @COUNTS table([ACTION] varchar(28), [INSERTED] int, [UPDATED] int);
merge BBDW.[FACT_INTERACTIONRESPONSE] as t
using 
(
  select
      ir.[INTERACTIONRESPONSESYSTEMID],
      i.[INTERACTIONFACTID],
      ir.[RESPONSEDIMID],
      ir.[INTERACTIONRESPONSEDATE],
      ir.[INTERACTIONRESPONSEDATEDIMID],
      ir.[ISINCLUDED],
      ir.[ETLCONTROLID],
      ir.[SOURCEDIMID]
  from BBDW.[FACT_INTERACTIONRESPONSE_STAGE] ir
  inner join BBDW.[FACT_INTERACTION] i on i.[INTERACTIONSYSTEMID] =  ir.[INTERACTIONSYSTEMID]
) as s
on (t.[INTERACTIONRESPONSESYSTEMID] = s.[INTERACTIONRESPONSESYSTEMID])
when not matched by target
  then insert(
   [INTERACTIONRESPONSESYSTEMID],
   [INTERACTIONFACTID],
   [RESPONSEDIMID],
   [INTERACTIONRESPONSEDATEDIMID],
   [INTERACTIONRESPONSEDATE],
   [ISINCLUDED],
   [ETLCONTROLID],
   [SOURCEDIMID]
  ) values (
    s.[INTERACTIONRESPONSESYSTEMID],
    s.[INTERACTIONFACTID],
    s.[RESPONSEDIMID],
    s.[INTERACTIONRESPONSEDATEDIMID],
    s.[INTERACTIONRESPONSEDATE],
    s.[ISINCLUDED],
    s.[ETLCONTROLID],
    s.[SOURCEDIMID]
  )
when matched
  then update
    set 
    t.[INTERACTIONRESPONSESYSTEMID] = s.[INTERACTIONRESPONSESYSTEMID],
    t.[INTERACTIONFACTID] = s.[INTERACTIONFACTID],
    t.[RESPONSEDIMID] = s.[RESPONSEDIMID],
    t.[INTERACTIONRESPONSEDATEDIMID] = s.[INTERACTIONRESPONSEDATEDIMID],
    t.[INTERACTIONRESPONSEDATE] = s.[INTERACTIONRESPONSEDATE],
    t.[ISINCLUDED] = s.[ISINCLUDED],
    t.[ETLCONTROLID] = s.[ETLCONTROLID],
    t.[SOURCEDIMID] = s.[SOURCEDIMID]
  output $action, 
  case when deleted.[ETLCONTROLID] is null then 1 else 0 end,
  case when deleted.[ETLCONTROLID] is not null then 1 else 0 end into @COUNTS;
select  count(*) as [TOTAL], isnull(sum([INSERTED]),0) as [INSERTED], isnull(sum([UPDATED]),0) as [UPDATED]
from @COUNTS