BulkUpsert

Use sfdb.BulkUpsert to insert new data or update existing data into Salesforce. It uses the Upsert method of Salesforce's Bulk Api.

An External ID column will be used to determine if the record already exists in Salesforce or not. If a record in Salesforce has the same External ID as a record in the source data, then that record is updated; otherwise, a new record is created in Salesforce.

Source Data

sfdb.BulkUpsert can use either a table or a view as the source of data to upload. The table or view can have any name, but the columns must be named as the Salesfore API Names of the fields that the data needs to be inserted in to.

You must specify at least one External ID field, and this field's name must be passed to the @externalIdColumnName parameter.

For example, to upsert some accounts, your source data might look like this:

NameOwnerIdExternal_System_Id__c
The Low Lights Tavern00520000002hD5hAAE123
The Ship's Cat00520000002hD5hAAE456
The Staith House00520000002hD5hAAE789

Read Data Basics for more information on how SFDB handles data.

Parameters

@sourceTableName

Data Type: NVARCHAR
Required: ️️✔️

The name of the table or view that contains the data that you'd like to upsert to Salesforce.

@objectName

Data Type: NVARCHAR
Required: ️️✔️

The API name of the object that you would like to upsert the data in to. e.g. Account, Contact, My_Custom_Object__c etc...

@externalIdColumnName

Data Type: NVARCHAR
Required: ️️✔️

The API name of the External ID field that the upsert operation will use to match records in the source data with records in Salesforce.

@resultsTableName

Data Type: NVARCHAR
Required: ️️✔️

The name of the table that should be created to store the results of the job in. If a table with the same name already exists, it will get dropped as part of this process. See Results.

@batchSize

Data Type: NVARCHAR
Required: ❌
Default: 10,000

Data will get be sent to Salesforce in batches. This parameter allows you to control the size of those batches.

@Context

Data Type: NVARCHAR
Required: ❌
Default: NULL

The context that this job should be made under. For more information, see Contexts.

Results

The results from the API call to Salesforce will be inserted into a table as defined by the value passed to the @resultsTableName parameter. You may need to check the results to ensure that the data was inserted as you expected.

The results table will contain these columns:

NameDescription
<externalIdColumnName>The column that was passed to the @externalIdColumnName parameter.
IdThe 18 digit Salesforce ID assigned to the record that was created or updated.
Success1 if this record was inserted or updated (otherwise 0).
Created1 if this record was inserted (otherwise 0).
ErrorThe error that prevented this record from being inserted or updated (if any). The source of this value is Salesforce (as opposed to SFDB).

Examples

Upsert Accounts from a table called AllAccounts, and put the results into a table called AccountUpsertResults. The External ID column name is External_System_Id__c.

EXEC sfdb.BulkUpsert 'AllAccounts', 'Account', 'External_System_Id__c', 'AccountUpsertResults'

Upsert Accounts from a view called YesterdaysAccounts, and put the results into a table called UpsertedAccounts. The External ID column name is External_System_Id__c, and batch size is 5,000.

EXEC sfdb.BulkUpsert 'YesterdaysAccounts', 'Account', 'External_System_Id__c', 'UpsertedAccounts', batchSize:5000

-- Failed records:
SELECT results.Error, source.*
FROM YesterdaysAccounts source
    INNER JOIN UpsertedAccounts results
        ON source.External_System_Id__c = results.External_System_Id__c
WHERE results.Success = 0

-- Inserted records:
SELECT source.*
FROM YesterdaysAccounts source
    INNER JOIN UpsertedAccounts results
        ON source.External_System_Id__c = results.External_System_Id__c
WHERE results.Created = 1

-- Updated records:
SELECT source.*
FROM YesterdaysAccounts source
    INNER JOIN UpsertedAccounts results
        ON source.External_System_Id__c = results.External_System_Id__c
WHERE results.Success = 1 
    AND results.Created = 0
← BulkUpdateBulkDelete →