SSIS Components for FinancialForce

Build 20.0.7656

UPSERT Statements

An UPSERT statement updates an existing record or creates a new record if an existing record is not identified.

Configuring Upserts

Upserts can only be performed on a field explicitly defined in FinancialForce to be an external key. You need to provide the name of this field in a column called ExternalIdColumn, as shown in the following query:

  UPSERT INTO Lead (FirstName, LastName, Company, External_Id_Column__c, ExternalIdColumn) 
  VALUES ('Bob', 'Thorton', 'Universal Pictures', 12345, 'External_Id_Column__c')

In order for the ExternalIdColumn to show up, modify the FinancialForce connection properties to set the PseudoColumns field to the value '*=*' without the quotes.

An UPSERT statement updates an existing record or create a new record if an existing record is not identified.

UPSERT Syntax

The UPSERT syntax is the same as for insert. FinancialForce uses the input provided in the VALUES clause to determine whether the record already exists. If the record does not exist, all columns required to insert the record must be specified. See Data Model for any table-specific information.

UPSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7656