Help > ODBC Drivers > Salesforce Driver > SQL Query Examples

Driver SQL Syntax

On this page you will find various query examples for Salesforce ODBC Driver / Data Gateway Connector.

Salesforce Query Language Reference

Salesforce driver offers executing query on server side using native SQL. Click on the below link to learn more about Salesforce Query Syntax.
Native SQL Help Page: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm

INSERT Syntax

INSERT INTO table_name ( column_name_1 [, column_name_2] ... ) 
VALUES (value1 [, value2] ... )[;]
[WITH (option_name=option_value] [, ...]) ] [;]

INSERT Bulk Syntax

INSERT INTO table_name ( column_name_1 [, column_name_2] ... ) 
[WITH (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
     ,'driver_specific_connection_string'
     ,'driver_specific_sql_query'
] [;]

UPDATE Syntax

UPDATE table_name 
SET column_name_1 = some_value 
  [,column_name_2 = some_value]
  ....
[WITH (option_name=option_value] [, ...]) ] [;]

UPDATE Bulk Syntax

UPDATE table_name 
[WITH (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
     ,'driver_specific_connection_string'
     ,'driver_specific_sql_query'
] [;]

DELETE Syntax

DELETE FROM table_name 
WHERE condition[WITH (option_name=option_value] [, ...]) ] [;]

DELETE Bulk Syntax

DELETE FROM table_name 
[WITH (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
     ,'driver_specific_connection_string'
     ,'driver_specific_sql_query'
] [;]

SQL Examples

SOQL Query - Basic Query

This example shows how to write simple SOQL query (Salesforce Object Query Language). It uses WHERE clause, ORDER BY and LIMIT clause. Using limit clause it will return only 10 records at maximum.

SOQL is similar to database SQL query language but much simpler and many features you use in database query may not be supported in SOQL (Such as JOIN clause not supported)

For more information about SOQL query syntax check below links :
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm

SELECT Id, LastName, FirstName 
FROM Contact
Where LastName!='Smith'
ORDER BY LastName, FirstName
LIMIT 10

SOQL Query - Parent fields (Child-to-Parent / Many-to-One)

This example shows how to get field value from related parent object using dot operator.

For more information about relationships query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

SELECT Id,FirstName, LastName, Account.Name FROM Contact

SOQL Query - Child records (Parent-to-Child / One-to-Many)

This example shows how to get child records for selected parent records (e.g. All contacts for specified accounts).

For more information about relationships query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

SELECT Account.Id,Account.Name
    , (SELECT Contact.FirstName, Contact.LastName, Contact.Email FROM Account.Contacts) 
FROM Account

SOQL Query - Child Query with Order By and LIMIT

This example shows how to use child query and limit number or child records for each parent row.

SELECT Name, 
    (SELECT FirstName, LastName FROM Contacts ORDER BY CreatedDate LIMIT 5) 
FROM Account
Where Name='ZappySys'

SOQL Query - Object and field alias

This example shows how to alias Table name or field name in SOQL queries. SOQL has very limited support for alias. You can alias Table name in any query but field alias is only allowed in Aggregation query only (Query with at least one aggregation function).

For more information about Alias in SOQL query check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_alias.htm?search_text=Alias
For more information about Alias in Aggregation query check this link: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby_alias.htm?search_text=Alias

SELECT c.Id, c.LastName, a.Name 
FROM Contact c,c.Account a
LIMIT 10

SOQL Query - GROUP BY / Aggregate function

This example shows how to use GROUP BY clause along with optional HAVING clause. When you use Aggregate function in SQL Query you can also use alias for field (This is the only time alias can be used). Child query cannot be used when Aggregate functions are used. Refer help file to learn more about other consideration.

For more information about GROUP BY check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby.htm

SELECT LeadSource, COUNT(Name) Cnt
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100

SOQL Query - GROUP BY / Aggregate function

This example shows how to use COUNT function. You can use COUNT without or with WHERE / GROUP BY clause too.

For more information about COUNT check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_count.htm

SELECT COUNT(Id) Total FROM Account Where Name LIKE 'A%'

SOQL Query with DateTime / Use of variable

This example shows how to use date/time in WHERE clause. Date and time must be yyyy-MM-ddTHH:mm:ssZ format (make sure to include time part and add Z for UTC indicator after date and time just like example). You can use variable placeholders anywhere in the query to make it dynamic. You can use inbuilt Date Literals (e.g. TODAY, YESTERDAY, LAST_N_DAYS:365). By default when variable with DateTime type is used then its automatically converted to UTC datetime format before sending query to server. If you want to query by UTC Date only (without time portion from variable) then use TO_UTC_DATE rather than TO_UTC_DATETIME format specifier (e.g. SELECT Id, FirstName FROM Contact WHERE CreatedDate < {{System::ContainerStartTime,TO_UTC_DATE}} ). To convert UTC date variable to Local Date use TO_LOCAL_DATE or TO_LOCAL_DATETIME

For more information about query datetime field check this link : https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm?search_text=date
For more information about variable placeholder usage check this link : https://zappysys.com/onlinehelp/ssis-powerpack/scr/ssis-format-specifiers.htm

SELECT Id, LastName, FirstName, Email, Account.Name 
FROM Contact
Where LastModifiedDate < {{System::ContainerStartTime,TO_UTC_DATETIME}} AND LastModifiedDate > 1999-12-31T00:00:00Z AND CreatedDate < YESTERDAY
LIMIT 10

SOQL Query - IN clause with Subquery

This example shows how to use sub query result for IN clause.
For more information check this link:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm

SELECT Id, Name 
FROM Account
WHERE Id IN
  (
    SELECT AccountId
    FROM Contact
    WHERE LastName LIKE 'a%'
  )
  AND Id IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE isClosed = false
  )

SOQL Query - Use of special characters (i.e Escape sequence)

This example shows how to use reserved special characters inside string literals using escape sequence. Anytime you have reserved character in string then use slash (\) in front of character e.g. Sam's Farm would be Sam\'s Farm.
For more information check below links:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_quotedstringescapes.htm
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_reservedcharacters.htm

SELECT Id FROM Account WHERE Name = 'Sam\'s Farm'

DML - UPDATE Example

This example shows how to use UPDATE one or more records in salesforce table. You can supply any valid SOQL compatible WHERE clause.

UPDATE account SET name='New Company Name', BillingState='GA' WHERE id IN ('0014N00001hTNEEQA4')

WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/ 

    --,IgnoreFieldsIfInputNull=1 --Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
    --,FieldsToSetNullIfInputNull='SomeColum1,SomeColumn5,SomeColumn7' --Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
    --,AssignmentRuleId='xxxxx' --rule id to invoke on value assignment
    --,UseDefaultAssignmentRule=1 --sets whether you like to use default rule
    --,AllOrNone=1 --If true, any failed records in a call cause all changes for the call to be rolled back. Record changes aren't committed unless all records are processed successfully. The default is false. Some records can be processed successfully while others are marked as failed in the call results.
    --,AllowFieldTruncation=1 --If true, truncate field values that are too long, which is the behavior in API versions 14.0 and earlier.
    --,AllowSaveOnDuplicates=1 --Set to true to save the duplicate record. Set to false to prevent the duplicate record from being saved.
    --,EnableParallelThreads=1 --Enables sending Data in multiple threads to speedup. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,MaxParallelThreads=6 --Maximum threads to spin off to speedup write operation. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,TempStorageMode='Disk' --or 'Memory'. Use this option to overcome OutOfMemory Error if you processing many rows. This option enables how Temp Storage is used for query processing. Available options 'Disk' or 'Memory' (Default is Memory)

)

DML - INSERT Example

This example shows how to create a new record in salesforce table.

INSERT INTO Account(Name, BillingCity,PK__c)
VALUES ('mycompany name', 'New York','K100')

WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/ 

    --,IgnoreFieldsIfInputNull=1 --Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
    --,FieldsToSetNullIfInputNull='SomeColum1,SomeColumn5,SomeColumn7' --Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
    --,AssignmentRuleId='xxxxx' --rule id to invoke on value assignment
    --,UseDefaultAssignmentRule=1 --sets whether you like to use default rule
    --,AllOrNone=1 --If true, any failed records in a call cause all changes for the call to be rolled back. Record changes aren't committed unless all records are processed successfully. The default is false. Some records can be processed successfully while others are marked as failed in the call results.
    --,AllowFieldTruncation=1 --If true, truncate field values that are too long, which is the behavior in API versions 14.0 and earlier.
    --,AllowSaveOnDuplicates=1 --Set to true to save the duplicate record. Set to false to prevent the duplicate record from being saved.
    --,EnableParallelThreads=1 --Enables sending Data in multiple threads to speedup. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,MaxParallelThreads=6 --Maximum threads to spin off to speedup write operation. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,TempStorageMode='Disk' --or 'Memory'. Use this option to overcome OutOfMemory Error if you processing many rows. This option enables how Temp Storage is used for query processing. Available options 'Disk' or 'Memory' (Default is Memory)

)

DML - DELETE Example

This example shows how to delete one or more records in salesforce table. You can supply any valid SOQL compatible WHERE clause.

DELETE FROM account WHERE id IN ('0014N00001hTNEEQA4')

WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/ 

    --,IgnoreFieldsIfInputNull=1 --Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
    --,FieldsToSetNullIfInputNull='SomeColum1,SomeColumn5,SomeColumn7' --Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
    --,AssignmentRuleId='xxxxx' --rule id to invoke on value assignment
    --,UseDefaultAssignmentRule=1 --sets whether you like to use default rule
    --,AllOrNone=1 --If true, any failed records in a call cause all changes for the call to be rolled back. Record changes aren't committed unless all records are processed successfully. The default is false. Some records can be processed successfully while others are marked as failed in the call results.
    --,AllowFieldTruncation=1 --If true, truncate field values that are too long, which is the behavior in API versions 14.0 and earlier.
    --,AllowSaveOnDuplicates=1 --Set to true to save the duplicate record. Set to false to prevent the duplicate record from being saved.
    --,EnableParallelThreads=1 --Enables sending Data in multiple threads to speedup. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,MaxParallelThreads=6 --Maximum threads to spin off to speedup write operation. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,TempStorageMode='Disk' --or 'Memory'. Use this option to overcome OutOfMemory Error if you processing many rows. This option enables how Temp Storage is used for query processing. Available options 'Disk' or 'Memory' (Default is Memory)

)

DML - UPSERT Example (Update or Insert)

This example shows how to Update or Insert record if not found (i.e. UPSERT). For Upsert You must supply Key field name in WITH clause. Driver lookup record based on Key column, if not found in salesforce then it creates a new row else update exising record. For Key field you can use id column (system field) or any valid external id field created using this method https://zappysys.com/links/?id=10098 .

UPSERT INTO Account(Name, BillingCity,SupplierId__c)
VALUES ('mycompany name', 'New York','K100')
WITH (KEY='SupplierId__c',Output=1)

External Input from MSSQL - INSERT multiple rows from MSSQL to Salesforce

This example shows how to call INSERT operation which takes multiple rows as input from external system (i.e. MSSQL, ODBC, OLEDB). In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query. In order to run this example service account used to run Data Gateway Service must have correct permission if you are using windows authentication.

INSERT INTO Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select ''Account001'' as Name,''City001'' as BillingCity,1 as NumberofLocations__c  UNION
          select ''Account002'' as Name,''City002'' as BillingCity,2 as NumberofLocations__c  UNION
          select ''Account003'' as Name,''City003'' as BillingCity,3 as NumberofLocations__c '  
          --query for external system (i.e. MSSQL). Output column names in query must match columns found in target table (i.e. Account). 
          --To see all possible columns in target, select table from dropdown on the preview tab
      )


WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/       

    --,EnableBulkMode=1 --use this to invoke Bulk API (usually slower for less number of rows, works better for large dataset 5K+)
    --OR--
      ,EnableParallelThreads=1 --//Send records in multiple threads for faster data processing (2000 rows sent in each batch). If you use EnableBulkMode=1 then this option is ignored and 500000 rows processed (in V2) in a each batch (in V1 its 10000) using Job Style API rather than Real Time API (faster for less than few hundred rows).
      ,MaxParallelThreads=6 --//Maximum threads to use for parallel processing

    --,IgnoreFieldsIfInputNull=1 --Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
    --,FieldsToSetNullIfInputNull='SomeColum1,SomeColumn5,SomeColumn7' --Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
    --,AssignmentRuleId='xxxxx' --rule id to invoke on value assignment
    --,UseDefaultAssignmentRule=1 --sets whether you like to use default rule
    --,AllOrNone=1 --If true, any failed records in a call cause all changes for the call to be rolled back. Record changes aren't committed unless all records are processed successfully. The default is false. Some records can be processed successfully while others are marked as failed in the call results.
    --,AllowFieldTruncation=1 --If true, truncate field values that are too long, which is the behavior in API versions 14.0 and earlier.
    --,AllowSaveOnDuplicates=1 --Set to true to save the duplicate record. Set to false to prevent the duplicate record from being saved.
    --,EnableParallelThreads=1 --Enables sending Data in multiple threads to speedup. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,MaxParallelThreads=6 --Maximum threads to spin off to speedup write operation. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,TempStorageMode='Disk' --or 'Memory'. Use this option to overcome OutOfMemory Error if you processing many rows. This option enables how Temp Storage is used for query processing. Available options 'Disk' or 'Memory' (Default is Memory)

)

External Input from ODBC - INSERT multiple rows from ODBC Source (i.e. CSV) to Salesforce

This example shows how to call INSERT operation which takes multiple rows as input from external system (i.e. MSSQL, ODBC, OLEDB). In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query.

INSERT INTO Account
SOURCE('ODBC' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Driver={ZappySys CSV Driver};DataPath=c:\somefile.csv'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'SELECT Acct_Name as Name,Billing_City as BillingCity,Locations as  NumberofLocations__c
FROM $
WITH( 
--You can use SRC=xxxxxx or static data using DATA=xxxxxx attribute below (either or). For example purpose we use static data but in real world you will read from file so comment DATA=xxx
--SRC=''c:\file_1.csv''
--SRC=''c:\some*.csv''
--SRC=''https://abc.com/api/somedata-in-csv''
DATA = ''Acct_Name,Billing_City,Locations
Account001,City001,1
Account002,City002,2
Account003,City003,3
''  
 )'
)
--query for external system (i.e. MSSQL). Output column names in query must match columns found in target table (i.e. Account) this is the reason we used alias in names.
--To see all possible columns in target, select table from dropdown on the preview tab


WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/       

    --,EnableBulkMode=1 --use this to invoke Bulk API (usually slower for less number of rows, works better for large dataset 5K+)
    --OR--
      ,EnableParallelThreads=1 --//Send records in multiple threads for faster data processing (2000 rows sent in each batch). If you use EnableBulkMode=1 then this option is ignored and 500000 rows processed (in V2) in a each batch (in V1 its 10000) using Job Style API rather than Real Time API (faster for less than few hundred rows).
      ,MaxParallelThreads=6 --//Maximum threads to use for parallel processing

    --,IgnoreFieldsIfInputNull=1 --Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
    --,FieldsToSetNullIfInputNull='SomeColum1,SomeColumn5,SomeColumn7' --Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
    --,AssignmentRuleId='xxxxx' --rule id to invoke on value assignment
    --,UseDefaultAssignmentRule=1 --sets whether you like to use default rule
    --,AllOrNone=1 --If true, any failed records in a call cause all changes for the call to be rolled back. Record changes aren't committed unless all records are processed successfully. The default is false. Some records can be processed successfully while others are marked as failed in the call results.
    --,AllowFieldTruncation=1 --If true, truncate field values that are too long, which is the behavior in API versions 14.0 and earlier.
    --,AllowSaveOnDuplicates=1 --Set to true to save the duplicate record. Set to false to prevent the duplicate record from being saved.
    --,EnableParallelThreads=1 --Enables sending Data in multiple threads to speedup. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,MaxParallelThreads=6 --Maximum threads to spin off to speedup write operation. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,TempStorageMode='Disk' --or 'Memory'. Use this option to overcome OutOfMemory Error if you processing many rows. This option enables how Temp Storage is used for query processing. Available options 'Disk' or 'Memory' (Default is Memory)

)

Bulk Api - INSERT large amount of data from External Source (i.e. MSSQL) to Salesforce

This example shows use EnableBulkMode option to insert large amount of data to salesforce (Invoke Job Style Bulk API). By default 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only few hundred rows. In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query. In order to run this example service account used to run Data Gateway Service must have correct permission if you are using windows authentication.

INSERT INTO Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select top 1000000 C_NAME as Name,C_CITY as BillingCity, C_LOC as NumberofLocations__c  From very_large_staging_table'
      )
WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/ 
      ,EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200) 
    --,MaxRowsPerJob=500000 --useful to control memory footprint in driver
    --,ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' - Must set BulkApiVersion=2 to use this, Bulk API V1 doesnt support this yet. If you get locking errors then change to Serial*/
    --,BulkApiVersion=2 --default is V1
    --,IgnoreFieldsIfInputNull=1 --Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
    --,FieldsToSetNullIfInputNull='SomeColum1,SomeColumn5,SomeColumn7' --Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
    --,AssignmentRuleId='xxxxx' --rule id to invoke on value assignment
    --,UseDefaultAssignmentRule=1 --sets whether you like to use default rule
    --,AllOrNone=1 --If true, any failed records in a call cause all changes for the call to be rolled back. Record changes aren't committed unless all records are processed successfully. The default is false. Some records can be processed successfully while others are marked as failed in the call results.
    --,AllowFieldTruncation=1 --If true, truncate field values that are too long, which is the behavior in API versions 14.0 and earlier.
    --,AllowSaveOnDuplicates=1 --Set to true to save the duplicate record. Set to false to prevent the duplicate record from being saved.
    --,EnableParallelThreads=1 --Enables sending Data in multiple threads to speedup. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,MaxParallelThreads=6 --Maximum threads to spin off to speedup write operation. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,TempStorageMode='Disk' --or 'Memory'. Use this option to overcome OutOfMemory Error if you processing many rows. This option enables how Temp Storage is used for query processing. Available options 'Disk' or 'Memory' (Default is Memory)

)

Bulk Api - DELETE large amount of data (Read Ids from External Source)

This example shows use EnableBulkMode option to delete large amount of records from salesforce (Invoke Job Style Bulk API). For delete you must pass Id column from source (if source has different name make sure to alias to Id in SQL). By default 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only few hundred rows. In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query. In order to run this example service account used to run Data Gateway Service must have correct permission if you are using windows authentication.

DELETE FROM Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select top 1000000 Account_ID as Id From very_large_staging_table'
      )
WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/ 
      ,EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200) 
    --,MaxRowsPerJob=500000 --useful to control memory footprint in driver
    --,ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' - Must set BulkApiVersion=2 to use this, Bulk API V1 doesnt support this yet. If you get locking errors then change to Serial*/
    --,BulkApiVersion=2 --default is V1
    --,IgnoreFieldsIfInputNull=1 --Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
    --,FieldsToSetNullIfInputNull='SomeColum1,SomeColumn5,SomeColumn7' --Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
    --,AssignmentRuleId='xxxxx' --rule id to invoke on value assignment
    --,UseDefaultAssignmentRule=1 --sets whether you like to use default rule
    --,AllOrNone=1 --If true, any failed records in a call cause all changes for the call to be rolled back. Record changes aren't committed unless all records are processed successfully. The default is false. Some records can be processed successfully while others are marked as failed in the call results.
    --,AllowFieldTruncation=1 --If true, truncate field values that are too long, which is the behavior in API versions 14.0 and earlier.
    --,AllowSaveOnDuplicates=1 --Set to true to save the duplicate record. Set to false to prevent the duplicate record from being saved.
    --,EnableParallelThreads=1 --Enables sending Data in multiple threads to speedup. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,MaxParallelThreads=6 --Maximum threads to spin off to speedup write operation. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,TempStorageMode='Disk' --or 'Memory'. Use this option to overcome OutOfMemory Error if you processing many rows. This option enables how Temp Storage is used for query processing. Available options 'Disk' or 'Memory' (Default is Memory)

)

Bulk Api - UPDATE large amount of data (Read from External Source)

This example shows use EnableBulkMode option to update large amount of data to salesforce (Invoke Job Style Bulk API). For update you must pass Id column from source (if source has different name make sure to alias to Id in SQL). By default 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only few hundred rows. In this example we specified Driver Type as MSSQL. For other system (i.e. CSV, API, ORACLE) change driver type to ODBC adjust connection string / query. Make sure query outputs column names which are found in Salesforce Table. EXTERNAL option maps target columns based on input query. In order to run this example service account used to run Data Gateway Service must have correct permission if you are using windows authentication.

UPDATE Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'  --driver specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select top 1000000 Account_ID as Id, Account_Name as Name,City as BillingCity From very_large_staging_table'
      )
WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When Output option is supplied then error is not thrown but you can capture status and message in __RowStatus and __ErrorMessage output columns*/ 
      ,EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200) 
    --,MaxRowsPerJob=500000 --useful to control memory footprint in driver
    --,ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' - Must set BulkApiVersion=2 to use this, Bulk API V1 doesnt support this yet. If you get locking errors then change to Serial*/
    --,BulkApiVersion=2 --default is V1
    --,IgnoreFieldsIfInputNull=1 --Set this option to True if you wish to ignore fields if input value is NULL. By default target field is set to NULL if input value is NULL.
    --,FieldsToSetNullIfInputNull='SomeColum1,SomeColumn5,SomeColumn7' --Comma separated CRM entity field names which you like to set as NULL when input value is NULL. This option is ignored if IgnoreFieldsIfInputNull is not set to True.
    --,AssignmentRuleId='xxxxx' --rule id to invoke on value assignment
    --,UseDefaultAssignmentRule=1 --sets whether you like to use default rule
    --,AllOrNone=1 --If true, any failed records in a call cause all changes for the call to be rolled back. Record changes aren't committed unless all records are processed successfully. The default is false. Some records can be processed successfully while others are marked as failed in the call results.
    --,AllowFieldTruncation=1 --If true, truncate field values that are too long, which is the behavior in API versions 14.0 and earlier.
    --,AllowSaveOnDuplicates=1 --Set to true to save the duplicate record. Set to false to prevent the duplicate record from being saved.
    --,EnableParallelThreads=1 --Enables sending Data in multiple threads to speedup. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,MaxParallelThreads=6 --Maximum threads to spin off to speedup write operation. This option is ignored when bulk mode enabled (i.e. EnableBulkMode=1)
    --,TempStorageMode='Disk' --or 'Memory'. Use this option to overcome OutOfMemory Error if you processing many rows. This option enables how Temp Storage is used for query processing. Available options 'Disk' or 'Memory' (Default is Memory)

)

---

Copyrights reserved. ZappySys LLC.