BulkDelete

Use sfdb.BulkDelete to delete data from Salesforce. It supports both regular deletes and hard deletes uses the Delete or HardDelete methods of Salesforce's Bulk Api.

Source Data

sfdb.BulkDelete can use either a table or a view as the source of data to upload. The table or view can have any name, and the only requirement is that it has a column called Id that contains the 18 character Salesforce IDs (e.g. 00520000002hD5hAAE) for the resords to be deleted.

You may include other columns, but they will be ignored by the procedure.

For example, your source data might look like this:

Id
0014J000008mpp3QAA
0014J000008mpp4QAA
0014J000008mpp5QAA
0014J000008mpp6QAA

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 IDs of the records that you'd like to delete.

@objectName

Data Type: NVARCHAR
Required: ️️✔️

The API name of the object that you would like to delete records from. e.g. Account, Contact, My_Custom_Object__c etc...

@resultsTableName

Data Type: NVARCHAR
Required: ❌
Default: NULL

The name of the table (if any) 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.

@isHardDelete

Data Type: BIT
Required: ❌
Default: 0

A flag to indicate if the records should be hard deleted or not. 1 for hard delete, 0 for regular delete. 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 deleted (otherwise 0).
Created	1 if this record was inserted (otherwise 0). This should always be 0 for BulkDelete.
Error	The error that prevented this record from being updated (if any). The source of this value is Salesforce (as opposed to SFDB).

Hard Delete

Usually, when a record is deleted from Salesforce, it is placed into the recycle bin so that it can be recovered if required. If you would like to bypass this, the you can perform what is known as a 'hard delete' by setting the @isHardDelete parameter to 1.

This is a non-reversable destructive change, so use with caution. You will need to allow the user account that SFDB authorizes with to perform hard deletes (Bulk API Hard Delete permission).

Examples

Note: there are no examples for hard delete- this is to reduce the risk of a hard delete being accidentally performed by a copy and paste error. You can enable hard delete for any of these examples by appending , @isHardDelete:1 to the end of the EXEC command.

Delete Opportunities from a table called ExpiredOpportunities, and put the results into a table called DeletedOpportunities

EXEC sfdb.BulkDelete 'ExpiredOpportunities', 'Opportunity', 'DeletedOpportunities'

Delete records from a custom object called Offline_Leads__c from a view called OptedOutLeads, and put the results into a table called DeletedLeads. Set a batch size of 1,000, and run a query to find failed records.

EXEC sfdb.BulkDelete 'OptedOutLeads', 'Offline_Leads__c', 'DeletedLeads', batchSize:1000

-- Failed records:
SELECT results.Error, source.*
FROM OptedOutLeads source
    INNER JOIN DeletedLeads results
        ON source.Id = results.Id
WHERE results.Success = 0