BulkUpdate
Use sfdb.BulkUpdate to update data in Salesforce. It uses the Update method of Salesforce's Bulk Api.
Source Data
sfdb.BulkUpdate 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 also include the 18 character Salesforce ID (e.g. 00520000002hD5hAAE) of each record that you'd like to update. This value must be in a column called Id. If you do not know this value, you can use sfdb.BulkUpsert instead.
For example, to update the Name and My_Custom_Field__c fields on some accounts, your source data might look like this:
| Id | Name | My_Custom_Field__c |
|---|---|---|
| 0014J000008mpp3QAA | Dodgin's Yard | Karen visted on Thursday |
| 0014J000008mpp4QAA | Salty Sea Dog | Expressed interest for 400 tapas plates |
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 update.
@objectName
Data Type: NVARCHAR
Required: ️️✔️
The API name of the object that you would like to update. e.g. Account, Contact, My_Custom_Object__c etc...
@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 updated as you expected.
The results table will contain these columns:
| Name | Description |
|---|---|
| Id | The 18 digit Salesforce ID for the record. |
| Success | 1 if this record was updated (otherwise 0). |
| Created | 1 if this record was inserted (otherwise 0). This should always be 0 for BulkUpdate |
| Error | The error that prevented this record from being updated (if any). The source of this value is Salesforce (as opposed to SFDB). |
Examples
Update Contacts from a table called ContactsToUpdate, and put the results into a table called ContactsResults
EXEC sfdb.BulkUpdate 'ContactsToUpdate', 'Contact', 'ContactsResults'
Update a custom object called Sales_Lines__c from a table called SalesCorrections, and put the results into a table called CorrectedSales. Set a batch size of 1,000, and run a query to find failed records.
EXEC sfdb.BulkInsert 'SalesCorrections', 'Sales_Lines__c', 'CorrectedSales', batchSize:1000
-- Failed records:
SELECT results.Error, source.*
FROM SalesCorrections source
INNER JOIN CorrectedSales results
ON source.Id = results.Id
WHERE results.Success = 0