Upsert with SQL Sales and working with the Salesfore api is a little different to the other operations. Update, Delete and Undelete all work from the provided Id, with regards what records to operate against. Insert merely creates new records and passes the new Id back.
Whereas Upsert, as the name suggests, works primarily off a provided External Id for the given object being upserted against.
If a match is found in that Salesforce object for the value being passed in, then the Operation will update the provided fields in the table payload to the matched record.
If a match is not found, the Upsert operation will insert a new record, using the provided fields in the table payload.
External Id
Upsert will only work against an External data type field. This is a special setting for a field in Salesforce:
For this example, the field "External_Id__c" has been added to the Account object, note the special indicator "(External ID)". If you intended External Id does not have this, it is likely not actually setup as an External Id, no matter what the field name is.
SQL Sales will inform if it is not truly an External Id, this is demonstrated in the following examples.
Create a load table Example (Prep)
This example creates a load table called "Account_TestLoad_Update", to populate the newly created External_Id__c with values, this is merely background preparation so this example can work off values in Salesforce. Remember, for Updates, the Id and Error columns are mandatory. All load operations require the Error column, whether success or failure, to guide and inform you. Update requires a populated Id column.
drop table if exists Account_TestLoad_Update
select
Id
,convert(nvarchar(255),null) as Error
,AccountNumber as External_Id__c
,External_Id__c as External_Id__c_Orig
into Account_TestLoad_Update
from Account
where AccountNumber is not null
Running the example (prep)
This is being provided as an educational guide to using ss_Loader and is not directly relevant for Upsert, the specific Upsert instructions are coming up in the next section
exec ss_Loader 'Update','DEMO','Account_TestLoad_Update'
Main Example
Examining the prepared Upsert payload, note that there is a column called "XId". This is the column which holds the value to be matched against the External Id specific to your use case (see detail coming up). Note also that three fields have values in XId, in fact two of these exist in Salesforce in field Account.External_Id__c, these will be matched and hence updated. The third value "XXXYYYZZZ" does not exist and so will be inserted. The fourth record has no value in XId and so will not be matched either, resulting in a failure as the External Id must be provided.
Running the example
Note, the XId column is assigned to the actual External api field name with the switch convention:
Upsert:XId=External_Id__c
exec ss_Loader 'Upsert:XId=External_Id__c','DEMO','Account_TestLoad_Upsert'
SQL-SALES Upsert:XId=External_Id__c run date: 2023-11-04 --------
19:59:24: Using Env|Schema: DEMO|dbo
19:59:26: Starting Loader for Account batchsize 200
19:59:26: SSId added to Account_TestLoad_Upsert
19:59:29: Connection method BULK & SOAP API
19:59:29: Columns checked against Salesforce metadata
19:59:32: Load complete: Success:3 Failure:1
-----------------------------------------------------------------
Here we can observe from the load table that as expected, three records are successful and the fourth has failed as no External Id has been provided.
The next test will be to run ss_Delta, where we expect 2 Updates and 1 Insert:
exec ss_Delta 'DEMO', 'Account'
SQL-SALES Delta run date: 2023-11-04 ----------------------------
20:03:53: Using Env|Schema: DEMO|dbo
20:03:53: Starting Delta Replication, checking input Account
20:03:58: Provided Objectname Account is valid with batchsize 2000
20:03:58: Delta schema successfully built using connection method BULK & SOAP API
20:03:58: Data population fully completed
20:03:58: 3 rows delta replicated: Insert:1 Update:2 Delete:0
-----------------------------------------------------------------
Querying the delta refreshed local Account table, we can see the pre-existing and matched-to records have been updated with the "__StringAdded" text appended to the Name field, whereas the unmatched third record has been inserted (created).
Invalid input
The field name passed in must be both a valid Salesforce field and crucially one which is defined as an External Id.
The below attempts are a field name that does not exist in Salesforce followed by a field name that does exist but which is not defined as an External Id
exec ss_Loader 'Upsert:XId=ExternalId','DEMO','Account_TestLoad_Upsert'
SQL-SALES Upsert:XId=ExternalId run date: 2023-11-04 ------------
20:06:43: Using Env|Schema: DEMO|dbo
20:06:46: Provided External Id: "ExternalId" for the Upsert operation
20:06:46: is not defined as an External Id on the Account object
-----------------------------------------------------------------
The final check is that the expected formatting convention of "Upsert:XId=<External field name>" for example "Upsert:XId=External_Id__c" does rely on the ":XId=" section being passed in correctly, below is an example where that has not been passed in as expected:
exec ss_Loader 'Upsert:X=External_Id__c','DEMO','Account_TestLoad_Upsert'
SQL-SALES Upsert:X=External_Id__c run date: 2023-11-04 ----------
20:10:02: Using Env|Schema: DEMO|dbo
20:10:02: Provided Upsert Operation value not in the correct format, for a hypothetical field called "External_Id__c"
20:10:02: set (in Salesforce) as an External Id (this is a field setting)
20:10:02: the expected input value for the standard web services API is: Upsert:XId=External_Id__c
20:10:02: or BulkAPIUpsert:XId=External_Id__c for the bulk API
20:10:02: with a defined batchsize (for example 100 or 1000 respectively) these would be:
20:10:02: Upsert(100):XId=External_Id__c | BulkAPIv1Upsert(10000):XId=External_Id__c | BulkAPIv2Upsert(2000):XId=External_Id__c
20:10:02: Note, the provided External Id is validated directly against Salesforce Account
20:10:02: prior to the run commencing to check it is actually defined as an External Id.
-----------------------------------------------------------------