BulkQuery

Use sfdb.BulkQuery to query and retrieve data from Salesforce. It supports optionally include deleted data in the results, and uses the Query or QueryAll methods of Salesforce's Bulk Api.

Parameters

@soqlQuery

Data Type: NVARCHAR
Required: ️️✔️

The SOQL query to execute to retrieve the data. See Valid SOQL Queries.

@objectName

Data Type: NVARCHAR
Required: ️️✔️

The API name of the object that you would like to query. 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 query in. If a table with the same name already exists, it will get dropped as part of this process. See Results.

@includeDeletedRecords

Data Type: BIT
Required: ❌
Default: 0

A flag to indicate if the deleted records that are currently in the Recycle Bin should be included in the query results.

@batchSize

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

Data will get be retrieved from 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.

Valid SOQL Queries

There are some limits on what constitutes a valid bulk SOQL query that are imposed by Salesforce's Bulk API:

  • The query cannot contain nested SOQL queries.
  • The query cannot contain more than 20,000 characters.
  • The following keywords cannot be used:
    • COUNT
    • ROLLUP
    • SUM
    • GROUP BY CUBE
    • OFFSET
  • The execution time of each query is limited. Reducing the batch size might help if you are encountering this limit.

SFDB does not currently support relationship queries.

Conversely, SFDB adds the ability to use the * selector in the SELECT clause, a feature that is not natively supported by Salesforce. e.g. SELECT * FROM Contact is a valid SOQL query in the context of SFDB, but it would not be a valid query if you tried to run it directly in Salesforce.

NOTE: If using the * selector, SFDB will substitute the * character for a comma seperated list of all the available columns on the object being queried. If the object has a lot of field then there is a chance that the query will fail because the total length of the query exceeds 20,000 characters.

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. The data type of each column will be determined by the rules described at Data Type Mapping

The order of the retrieved data is not guaranteed.

Examples

Select all fields from all Account, and put the results into a table called Accounts

EXEC sfdb.BulkQuery 'SELECT * FROM Account', 'Account', 'Accounts'

Select all the contacts whose first name is Chris, and put the results into a table called EveryChris

EXEC sfdb.BulkQuery 'SELECT Id, FirstName, LastName FROM Contact WHERE FirstName = ''Chris''', 'Contact', 'EveryChris'
← BulkInsertBulkRefresh →