• Usage
• Syntax
• Example
  • Restrictions
  • Examples of Valid and Non-Valid SOQL Queries

Usage

SF_BulkSOQL_Refresh compares the current, local SQL table containing the results of a SOQL query with that same SOQL query for a given time. The SOQL query used is in the _SOQL table produced by the SF_BulkSOQL procedure. Any changes (insert, deletes, or updates) are detected and the local table is updated.

See SF_BulkSOQL for more information on how to create a local SQL table with the results of a SOQL query.

Note that SF_BulkSOQL_Refresh uses the SOAP API to update the local SQL table.

Syntax

 EXEC SF_BulkSOQL_Refresh 'table_server','table_name'

where table_server is the name of your linked server and table_name is the name of the Results table.

Example

This example creates a local AccountsContacts table this the results from the SOQL query:

 EXEC SF_BulkSOQL 'SALESFORCE', 'AccountsContacts', '', 'SELECT account.name, owner.name, * FROM Contact'

This example refreshes the local AccountsContacts table using the SOQL query located in the _SOQL table that SF_BulkSOQL produces:

 EXEC SF_BulkSOQL_Refresh 'SALESFORCE', 'AccountsContacts'

Restrictions

SF_BulkSOQL_Refresh restrictions are:

• the SOQL query provided to SF_BulkSOQL cannot contain a WHERE clause

• the ID and SystemModstamp fields must be in the SELECT clause of the SOQL query

• the SOQL query cannot have any sub-selects in the SELECT clause

• see all BulkAPI restrictions placed on SOQL queries here: https://developer.Salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/asynch_api_using_bulk_query.htm

Examples of Valid and Non-Valid SOQL Queries

Below are examples of valid SOQL queries:

 'SELECT account.name, owner.name, * FROM Contact'

 'SELECT * FROM Account'

 'SELECT account.name, owner.name, ID, SystemModstamp FROM Contact'

 'SELECT ID, categories__c, location__c, Name, SystemModstamp FROM Book__c'

Below are examples of non-valid SOQL queries:

 'SELECT ID, FirstName, LastName FROM Contact'

  SELECT * FROM Account where AnnualRevenue > 1000'

  SELECT ID, (SELECT Description FROM ActivityHistories) FROM Account'

  SELECT Count(name), Count_distinct(name) FROM account'