BulkInsert
Use sfdb.BulkInsert to insert data into Salesforce. It uses the Insert method of Salesforce's Bulk Api.
Source Data
sfdb.BulkInsert 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.
For example, to insert some accounts, your source data might look like this:
| Name | OwnerId | My_Custom_Field__c |
|---|---|---|
| The Low Lights Tavern | 00520000002hD5hAAE | NULL |
| The Ship's Cat | 00520000002hD5hAAE | Closes early on a Tuesday |
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 insert into Salesforce.
@objectName
Data Type: NVARCHAR
Required: ️️✔️
The API name of the object that you would like to insert the data in to. 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.
@reconcilliationColumnName
Data Type: NVARCHAR
Required: ❌
Default: NULL
If specified, the reconcilliation column will be included in the results table, and you can use it to join the source data to the results data. It should be a unique value for each record if you intend to use it that way. For example- this could be a custom field on an object that contains the ID of the record from an external system.
@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:
| Name | Description |
|---|---|
| Id | The 18 digit Salesforce ID assigned to the record that was created. |
| Success | 1 if this record was inserted (otherwise 0). |
| Created | 1 if this record was inserted (otherwise 0). |
| Error | The error that prevented this record from being inserted (if any). The source of this value is Salesforce (as opposed to SFDB). |
| <ReconcilliationColumn> | The column (if any) that was passed to the @reconcilliationColumnName parameter. |
Examples
Insert Accounts from a table called NewAccounts, and put the results into a table called NewAccountsResults
EXEC sfdb.BulkInsert 'NewAccounts', 'Account', 'NewAccountsResults'
Insert Accounts from a table called AllAccounts, and put the results into a table called UploadedAccounts. Set a batch size of 1,000, and use the Name column as the reconcilliation column to find failed records.
EXEC sfdb.BulkInsert 'AllAccounts', 'Account', 'UploadedAccounts', reconcilliationColumnName:'Name', batchSize:1000
-- Failed records:
SELECT results.Error, source.*
FROM AllAccounts source
INNER JOIN UploadedAccounts results
ON source.Name = results.Name
WHERE results.Success = 0