SLAM_query_by_id

This specialized query operation filters Salesforce data based on IDs provided in a filter table. It automatically handles SOQL length limitations by batching IDs and uses efficient IN clause filtering. The table is overwritten if it already exists.

The most common use-cases will apply to Salesforce objects that require filters when querying, e.g. ContentDocumentLink

You may also use this procedure to filter by other text fields, such as external ids. You must ensure any data is properly escaped as required by Salesforce

Performance tip: This procedure is designed for querying specific records when you have a list of IDs. It automatically batches IDs to stay within SOQL length limits and can handle large sets of IDs.

Parameters:

Name	Type	Default	Description
`@query`	`NVARCHAR`	Required	sObject name or SOQL query string. When just an sObject name is passed, queries all records & fields
`@table`	`VARCHAR`	Required	Target SQL table name for results
`@filter_table`	`VARCHAR`	Required	Source table containing IDs to filter by (must have an ID column)
`@queryall`	`BIT`	`0`	When 1, includes deleted/archived records
`@exists_action`	`VARCHAR(20)`	`SLAM_Settings.default_exists_action`	Action to take if the target object name specified by `@table` is already taken. If NULL, uses the value from `SLAM_Settings.default_exists_action` (defaulted to `'drop'` during initial setup). Valid options: `'drop'` `'rename'` `'fail'`

Usage Examples:

Query ContentDocumentLink records by ContentDocumentId using a filter table:

-- prepare filter table from ContentDocument records
EXEC dbo.SLAM_query_soap
    @query = 'SELECT Id FROM ContentDocument LIMIT 600',
    @table = 'ContentDocument_query'

CREATE TABLE ContentDocumentLink_filter(ContentDocumentId VARCHAR(18))
INSERT INTO ContentDocumentLink_filter(ContentDocumentId)
SELECT Id FROM ContentDocument_query

EXEC dbo.SLAM_query_by_id
    @query = 'ContentDocumentLink',
    @table = 'ContentDocumentLink_filtered',
    @filter_table = 'ContentDocumentLink_filter'

Query with deleted records included:

-- prepare filter table
CREATE TABLE Lead_filter(Id VARCHAR(18))
INSERT INTO Lead_filter(Id)
    <OUTPUT STATEMENT>

-- execute query
EXEC dbo.SLAM_query_by_id
    @query = 'SELECT Id, Name, Email, Status, IsDeleted FROM Lead',
    @table = 'Lead_filtered',
    @filter_table = 'Lead_filter',
    @queryall = 1

Query specific Contact fields using external ID filter:

-- prepare filter table
CREATE TABLE Contact_External_IDs(External_Id__c VARCHAR(255))
INSERT INTO Contact_External_IDs(External_Id__c)
    VALUES ('ExtId123'), ('ExtId456')

-- execute query
EXEC dbo.SLAM_query_by_id
    @query = 'SELECT Id, Name, Email, Account.Name FROM Contact',
    @table = 'Contact_filtered',
    @filter_table = 'Contact_External_IDs'

SLAM_query_by_id​

SLAM_query_by_id