SQL Query Examples

Content

Driver SQL Syntax

driver_syntax_native

SQL Examples

1. SOQL Query - Basic Query
2. SOQL Query - Parent fields (Child-to-Parent / Many-to-One)
3. SOQL Query - Get all columns (select * from)
4. SOQL Query - Read using Bulk API Mode (read large amount of data)
5. SOQL Query - Child records (Parent-to-Child / One-to-Many)
6. SOQL Query - Child Query with Order By and LIMIT
7. SOQL Query - Object and field alias
8. SOQL Query - GROUP BY / Aggregate function
9. SOQL Query - GROUP BY / Aggregate function
10. SOQL Query with DateTime / Use of variable
11. SOQL Query - IN clause with Subquery
12. SOQL Query - Use of special characters (i.e Escape sequence)
13. DML - UPDATE Example
14. DML - INSERT Example
15. DML - DELETE Example
16. DML - UPSERT Example (Update or Insert)
17. DML - UPDATE records owner with external id
18. DML - UPDATE records owner with OwnershipChange options
19. DML - UPDATE Lookup field value using External Id rather than Salesforce Id
20. DML - INSERT Lookup field value using External Id rather than Salesforce Id
21. DML - UPSERT Lookup field value using External Id rather than Salesforce Id
22. External Input from MSSQL - INSERT multiple rows from MSSQL to Salesforce
23. External Input from ODBC - INSERT multiple rows from ODBC Source (i.e. CSV) to Salesforce
24. Bulk Api - INSERT large amount of data from External Source (i.e. MSSQL) to Salesforce
25. Bulk Api - INSERT with lookup field (Read from External Source)
26. Bulk Api - DELETE large amount of data (Read Ids from External Source)
27. Bulk Api - UPDATE large amount of data (Read from External Source)
28. Bulk Api - UPDATE lookup field (Read from External Source)

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 - Get all columns (select * from)

This example shows how to use star to get all columns. By default SOQL query (Salesforce Object Query Language) doesnt allow star but ZappySys engine replaces star with all columns automatically.

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 * 
FROM Contact
Where LastName!='Smith'
ORDER BY LastName, FirstName
LIMIT 10

SOQL Query - Read using Bulk API Mode (read large amount of data)

This example shows how to query large amount of data using Bulk API. Bulk API can be invoked by two ways.
1. (SSIS or ODBC) Add #bulkmode at the front in your SELECT query
2. (SSIS only) On SSIS Salesforce Source UI check [Enable Bulk Mode] option.

We invoke Bulk API if we detect bulk indicator in the SOQL query or Option is set on the UI. Prefer bulk option if you have large amount of data to read. Do not set bulk option if you have small dataset.

For more information about Bulk API query limitations check below links:
https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/queries.htm

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

#bulkmode SELECT Id, LastName, FirstName 
FROM Contact
LIMIT 10

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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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 . If Operation is UPSERT rather than UPDATE then you can use Key='ExternalId_Field_Name' but for Update you must use Id like this example.

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

DML - UPDATE records owner with external id

This example shows how to use Update / Upsert / Insert record owner in salesforce table using Owner External Id field rather than using internal salesforce Id of User record. You can see commented Id='001xxxxxxxxQB2' thats what you would do in many cases but if you rely on Id coming from external system then try something like SET [Owner.Some_External_Id__c(User)]='SOME-EXT-ID-001' replace Some_External_Id__c with field defined as external Id in User table and also replace value for External ID field (e.g. SOME-EXT-ID-001 to something else

UPDATE account 
SET [Owner.Some_External_Id__c(User)]='SOME-EXT-ID-001' 
--OR use salesforce internal Id-- 
--SET Owner='001xxxxxxxxQB2' 
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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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 - UPDATE records owner with OwnershipChange options

This example shows how to supply OwnerChangeOptions when Owner of the record is is changed.

UPDATE account 
SET [Owner.Some_External_Id__c(User)]='SOME-EXT-ID-001' 
--OR use salesforce internal Id-- 
--SET Owner='001xxxxxxxxQB2' 
WHERE id IN ('0014N00001hTNEEQA4')
WITH( OwnerChangeOptions = 'TransferAllOwnedCases,TransferContracts,TransferOrders'
, AllowSaveOnDuplicates=0, AllowFieldTruncation=0, AllOrNone=0
)
--WITH(OwnerChangeOptions = 'TransferAllOwnedCases-n,TransferContracts-n,TransferOrders') -- In this line we added '-n' suffix which means do not execute TransferAllOwnedCases and TransferContracts but execute only TransferOrders
--Some possible values for OwnerChangeOptions are below
--		EnforceNewOwnerHasReadAccess
--		TransferOpenActivities
--		TransferNotesAndAttachments
--		TransferOthersOpenOpportunities
--		TransferOwnedOpenOpportunities
--		TransferOwnedClosedOpportunities
--		TransferOwnedOpenCases
--		TransferAllOwnedCases
--		TransferContracts
--		TransferOrders
--		TransferContacts
--		TransferArticleOwnedPublishedVersion
--		TransferArticleOwnedArchivedVersions
--		TransferArticleAllVersions
--		KeepAccountTeam
--		KeepSalesTeam
--		KeepSalesTeamGrantCurrentOwnerReadWriteAccess
--		SendEmai
--To see all possible values for OwnerChangeOptions visit https://zappysys.com/links/?id=10141

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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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 - UPDATE Lookup field value using External Id rather than Salesforce Id

This example shows how to set Lookup field value using external id in DML operation (e.g. update, insert, upsert). Typically we need to know Salesforce Id for the Lookup Field to change its value but if you want to set Lookup field using External Id rather than salesforce id then do like this example. Field name syntax: [relationship_name.external_field_name(child_object_name)]. Where (child_object_name) is optional. In this example [Owner.SomeExternalId__c(User)] is used as field name. Where 'Owner' is relationship name (For non-standard field it may be YourObjectname__r), SomeExternalId__c is custom field added in User Object which is marked as External Field and last part is Child Object name (i.e. related object) which contains external field. If you do not supply child_object_name then relation name (without __r suffix) is used as Object name to find the related record using external field. If you are reading input data using SOURCE(..) clause and you like to use BulkApiMode=1 option in WITH(...) clause then no need to supply object name in parenthesis. Just use relationship_name.external_id_field_name. To set NULL just use FieldName=null (e.g. AccountId=null) rather than relation_name.external_id_name(target_table)=null. Visit this link for more information https://zappysys.com/links/?id=10140

UPDATE account SET [Owner.SomeExternalId__c(User)]='USR0001' --where Owner is relation name and (User) is object name which contains SomeExternalId__c field (Note: __c suffix is used for custom field)
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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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 Lookup field value using External Id rather than Salesforce Id

INSERT INTO Account(Name, BillingCity, [Owner.SomeExternalId__c(User)] ) VALUES ('mycompany name', 'New York','K100')
--where Owner is relation name and (User) is object name which contains SomeExternalId__c field (Note: __c suffix is used for custom field)

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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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 Lookup field value using External Id rather than Salesforce Id

UPSERT INTO Account(Name, BillingCity,[Owner.SomeExternalId__c(User)])
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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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

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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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 with lookup field (Read from External Source)

This example shows use EnableBulkMode option to insert large amount of data to salesforce (Invoke Job Style Bulk API) also we are setting Owner using ExternalId of User object rather than Salesforce Id of Owner. For update you must pass Id column from source (if source has different name make sure to alias to Id in SQL). If Operation is UPSERT rather than UPDATE then you can use Key='ExternalId_Field_Name' but for Update you must use Id like this example. 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 Account_Name as Name,AccountOwnerId as [Owner.ExternalId] 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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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 lookup field (Read from External Source)

This example shows use EnableBulkMode option to update large amount of data to salesforce (Invoke Job Style Bulk API) also we are updating Owner using ExternalId of User object rather than Salesforce Id of Owner. For update you must pass Id column from source (if source has different name make sure to alias to Id in SQL). If Operation is UPSERT rather than UPDATE then you can use Key='ExternalId_Field_Name' but for Update you must use Id like this example. 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,AccountOwnerId as [Owner.ExternalId] 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.
    --,OwnerChangeOptions='option1,option2...optionN' -- use one or more options from below. Use '-n' suffix to disable option execution e.g. TransferOpenActivities-n
    -->>> Available owner change options: EnforceNewOwnerHasReadAccess,TransferOpenActivities,TransferNotesAndAttachments,TransferOthersOpenOpportunities,TransferOwnedOpenOpportunities,TransferOwnedClosedOpportunities,TransferOwnedOpenCases,TransferAllOwnedCases,TransferContracts,TransferOrders,TransferContacts,TransferArticleOwnedPublishedVersion,TransferArticleOwnedArchivedVersions,TransferArticleAllVersions,KeepAccountTeam,KeepSalesTeam,KeepSalesTeamGrantCurrentOwnerReadWriteAccess,SendEmail 
    -->>> For more information visit https://zappysys.com/link/?id=10141
    --,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)

)