As with the SOAP API Upsert, working with the Salesfore Bulk API v2 is a little different to the other operations. BulkAPIv2Update, BulkAPIv2Delete and BulkAPIv2Harddelete work from the provided Id, with regards what records to operate against. Insert merely creates new records and passes the new Id back.
Whereas BulkAPIv2Upsert, 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. All load operations require the Error column, whether success or failure, to guide and inform you.
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 (WAIT)
This example creates a load table called "Account_BulkAPIv2_Wait_Upsert".
drop table if exists Account_BulkAPIv2_Wait_Upsert
select top 33
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,Name
,'UPDATE_TEST_' + Id as AccountNumber
,External_Id__c
,AccountNumber as AccountNumber_Orig
into Account_BulkAPIv2_Wait_Upsert
from Account
where AccountNumber not like '0%'
and External_Id__c is not null
order by createddate desc
insert Account_BulkAPIv2_Wait_Upsert
(Id
,Error
,Name
,AccountNumber
,External_Id__c
,AccountNumber_Orig)
select top 33
convert(nchar(18),null) --Id
,convert(nvarchar(255),null) --Error
,Name
,'REJECT_TEST_' + Id --AccountNumber
,null --External_Id__c
,null --AccountNumber_Orig
from Account
where AccountNumber not like '0%'
and External_Id__c is not null
order by createddate desc
insert Account_BulkAPIv2_Wait_Upsert
(Id
,Error
,Name
,AccountNumber
,External_Id__c
,AccountNumber_Orig)
select top 33
convert(nchar(18),null) --Id
,convert(nvarchar(255),null) --Error
,Name
,'INSERT_TEST_' + Id --AccountNumber
,'INSERT_TEST_' + Id --AExternal_Id__c
,null --AccountNumber_Orig
from Account
where AccountNumber not like '0%'
and External_Id__c is not null
order by createddate desc
Check the Payload
Note, those records with a matched (to Salesforce) value in External_Id__c will result in an update, those with a new value but no match to Salesforce will result in an Insert, whereas those with no value in External_Id__c will be rejected (as External_Id__c, being the specified External Id field for the BulkAPIv1Upsert operation, es expected to be populated).
Running the example (incorrect)
exec ss_Loader 'BulkAPIv2Upsert', 'DEMO', 'Account_BulkAPIv2_Wait_Upsert','WAIT'
SQL-SALES BulkAPIv2Upsert run date: 2023-12-09 ------------------
22:02:11: Using Env|Schema: DEMO|dbo
22:02:11: Provided Upsert Operation value not in the correct format, for a hypothetical field called "External_Id__c"
22:02:11: set (in Salesforce) as an External Id (this is a field setting)
22:02:11: the expected input value for the standard web services API is: Upsert:XId=External_Id__c
22:02:11: or BulkAPIv1Upsert:XId=External_Id__c for the bulk API v1
22:02:11: or BulkAPIv2Upsert:XId=External_Id__c for the bulk API v2
22:02:11: with a defined batchsize (for example 100 or 1000 respectively) these would be:
22:02:11: Upsert(100):XId=External_Id__c | BulkAPIv1Upsert(10000):XId=External_Id__c | BulkAPIv2Upsert(2000):XId=External_Id__c
22:02:11: Note, the provided External Id is validated directly against Salesforce Account
22:02:11: prior to the run commencing to check it is actually defined as an External Id.
-----------------------------------------------------------------
Note, the format provided in the example is incorrect, observe the supporting help text
Running the example (correct)
exec ss_Loader 'BulkAPIv2Upsert:XId=External_Id__c', 'DEMO', 'Account_BulkAPIv2_Wait_Upsert','WAIT'
SQL-SALES BulkAPIv2Upsert:XId=External_Id__c run date: 2023-12-09
22:02:55: Using Env|Schema: DEMO|dbo
22:02:58: Starting Loader for Account batchsize 10000
22:02:58: SSId added to Account_BulkAPIv2_Wait_Upsert
22:03:01: Connection method BULK & BULK API
22:03:01: Bulk API method WAIT
22:03:01: Columns checked against Salesforce metadata
22:03:01: Starting load for Account_BulkAPIv2_Wait_Upsert
22:03:16: JobId: 7508d00000TtUJvAAN
22:03:16: Excluded: AccountNumber_Orig is not available on object Account
22:03:16: Load complete: Success:66 Failure:33
-----------------------------------------------------------------
Note the Job Id for your submission is returned in the output for your reference, see also the log table ss_BulkAPILog.
ss_BulkAPILog table
The Job Id is also preserved in the ss_BulkAPILog table, written on each submission.
Checking the load table
For Inserts and Version 2, no direct response of Ids and Error column values are written back to the Load table.
_Return helper table
The batch to which a given row has been allocated is logged in the helper table created in the run that is your load table + “_Return”, using the above example it is:
Account_BulkAPIv2_Wait_Upsert_Return
For Version 2, this provides the created Id and/or Error reason for information purposes, but the rows do not tire back to your load table.