Reference

Table [Dynamic Table]

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_[dynamic_endpoint_name]
INSERT post_[dynamic_endpoint_name]
UPDATE put_[dynamic_endpoint_name]
UPSERT upsert_[dynamic_endpoint_name]
DELETE delete_[dynamic_endpoint_name]
LOOKUP search_[dynamic_endpoint_name]

Examples

SSIS

Use Zoho CRM Connector in API Source component to read data or in API Destination component to read/write data:

Read from [Dynamic Table] table using API Source

API Source - Zoho CRM
Read and write Zoho CRM data effortlessly. Integrate, manage, and automate accounts, leads, contacts, and deals — almost no coding required.
Zoho CRM
[Dynamic Table]
Optional Parameters
Module [$parent.api_name$]
Fetch records modified after (local time format: yyyy-MM-ddTHH:mm:ss) 1900-12-31T00:00:00
Custom View ID
DisableChildEndPoint True
FieldsValue id
Fields (Only for V2 API)
sort_by
sort_order
territory_id
include_child
converted
PagingMode ByUrlParameter
NextUrlAttributeOrExpr $.info.next_page_token
NextUrlSuffix page_token=<%nextlink%>
SSIS API Source - Read from table or endpoint

Read/write to [Dynamic Table] table using API Destination

API Destination - Zoho CRM
Read and write Zoho CRM data effortlessly. Integrate, manage, and automate accounts, leads, contacts, and deals — almost no coding required.
Zoho CRM
[Dynamic Table]
Select
Optional Parameters
Module [$parent.api_name$]
Fetch records modified after (local time format: yyyy-MM-ddTHH:mm:ss) 1900-12-31T00:00:00
Custom View ID
DisableChildEndPoint True
FieldsValue id
Fields (Only for V2 API)
sort_by
sort_order
territory_id
include_child
converted
PagingMode ByUrlParameter
NextUrlAttributeOrExpr $.info.next_page_token
NextUrlSuffix page_token=<%nextlink%>
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read accounts

<p>Reads all records from the <code>Accounts</code> table. This example demonstrates a basic SELECT query to retrieve a complete list of accounts from Zoho CRM.</p> 

SELECT * from Accounts

Update record owner

<p>Updates the owner of a record (e.g., Account, Contact, Deal, Lead). This example shows how to update a lookup field like <code>Owner</code> using a unique identifier such as email or ID. In this case, the owner of a specific Account is updated by email.</p> 

UPDATE Accounts 
SET Owner='{email: "bob-the-salesman@abc.com"}'
--SET Owner='{id: "1558554000186378001"}' --you can also use Id
Where Id='1558554000137221573'

Update deal account or contact

<p>Updates the Account and Contact lookup fields for a Deal record. This example demonstrates setting the <code>Account_Name</code> by name and <code>Contact_Name</code> by ID for a specific Deal.</p> 

UPDATE Deals 
SET Account_Name='{"name": "Company ABCD"}',    --by name or id 
    Contact_Name='{"id": "1558554000186378001"}'  --by id 
Where Id='1558554000137221573'

Create a deal with lookup fields

<p>Creates a new Deal record with lookup fields populated. This example demonstrates how to set <code>Account_Name</code>, <code>Contact_Name</code>, and <code>Owner</code> using their respective Names, IDs, or Emails during the INSERT operation.</p> 

INSERT INTO Deals(
	Deal_name,
	Amount,
	Lead_Source,
	Account_Name,
	Contact_Name,
	Owner
	)
VALUES
   (
   'My Test Deal Creatyed on <<FUN_NOW>>', --deal name
   1000.50, --amount
   'Cold Call', --lead source
   '{name:"ZappySys"}', --account name or use id '{id:"1558554000030180013"}'
   '{id:"1558554000089352998"}', --contact id 
   '{id:"1558554000089352912"}' --owner id or use email {email: "bob-the-salesman@abc.com"}
   )

Read an account by ID

<p>Reads a single Account record by its ID. This example demonstrates filtering the <code>Accounts</code> table using the <code>WHERE</code> clause with a specific ID.</p> 

SELECT * from Accounts Where Id=1558554000105110008

Read accounts modified after date

<p>Reads Account records modified after a specified date and time. This example demonstrates using the <code>ModifiedSince</code> parameter in the <code>WITH</code> clause to perform an incremental fetch.</p> 

SELECT * from Accounts WITH(ModifiedSince = '2020-01-07T00:00:00')

Update a lead

<p>Updates a specific Lead record. This example demonstrates modifying fields like <code>Designation</code> and <code>Company</code> for a Lead identified by its ID in the <code>WHERE</code> clause.</p> 

Update Leads SET Designation='VP Sales', Company='Test' Where id=1558554000012181009

Update a lead (legacy)

<p>Updates a specific Lead record using an alternative syntax. This example demonstrates passing the <code>id</code> as a column in the <code>SET</code> clause instead of using a <code>WHERE</code> clause.</p> 

Update Leads SET id='1558554000012181009' /* id must be supplied */, Designation='VP Sales', Company='Test'

Read accounts modified after date (filtered)

<p>Reads Account records matching a name pattern and modified after a specific date. This example combines a <code>WHERE</code> clause filter with the <code>ModifiedSince</code> parameter for targeted data retrieval.</p> 

SELECT * from Accounts Where Account_Name LIKE 'Test%' WITH(ModifiedSince = '2020-01-07T00:00:00')

Search accounts by criteria

<p>Searches for Account records using specific criteria. This example demonstrates using the <code>search_Accounts</code> endpoint with the <code>criteria</code> parameter to perform a server-side search (e.g., accounts starting with "test").</p> 

SELECT * FROM search_Accounts WITH(criteria='Account_Name:starts_with:test')

Create an account

<p>Creates a new Account record. This example demonstrates using the <code>INSERT INTO</code> statement to add a new account with <code>Account_Name</code> and <code>Phone</code>.</p> 

INSERT INTO Accounts(Account_Name, Phone) VALUES('Company ABC','111-567-8888')

Create an account (with output)

<p>Creates a new Account record and returns the created record's details. This example uses <code>WITH(Output=1)</code> to display the result of the INSERT operation.</p> 

INSERT INTO Accounts(Account_Name, Phone) VALUES('Company ABC','111-567-8888') WITH(Output=1)

Delete an account

<p>Deletes a single Account record by its ID. This example demonstrates using the <code>DELETE FROM</code> statement with a <code>WHERE</code> clause specifying the record ID.</p> 

DELETE FROM Accounts WHERE id=11111111111

Delete accounts in bulk

<p>Deletes multiple Account records by specifying a list of IDs. This example demonstrates passing a comma-separated list of IDs to the <code>Id</code> parameter in the <code>WITH</code> clause (up to 100 IDs).</p> 

DELETE FROM Accounts WITH(Id='11111,22222,33333')

Upsert an account

<p>Updates or inserts an Account record. This example demonstrates using the <code>UPSERT INTO</code> statement, which checks for existing records based on unique fields before deciding to update or insert.</p> 

UPSERT INTO Accounts(Account_Name, Phone) VALUES('Company ABC','111-567-8888') WITH(Output=1)

Upsert a lead

<p>Updates or inserts a Lead record. This example demonstrates using the <code>UPSERT INTO</code> statement for Leads, updating based on unique fields like Email.</p> 

UPSERT INTO Leads(Last_Name, Email) VALUES('Patel','zpatel@abc.com') WITH(Output=1)

Bulk create accounts using SQL Server data

<p>Creates multiple Account records in bulk using data from a SQL Server database. This example demonstrates using the <code>SOURCE</code> clause to read data from an external ODBC/OLEDB source and insert it into Zoho CRM.</p> 

INSERT INTO Accounts
SOURCE(
  'MSSQL' --ODBC or OLEDB
  ,'Data Source=localhost;Initial Catalog=Test;Integrated Security=true'
  ,'select ''Test Account-A'' as Account_Name,''111-111-1111'' as Phone
    UNION
    select ''Test Account-B'' as Account_Name,''222-222-2222'' as Phone
   '
)

Bulk upsert accounts using SQL Server data

<p>Upserts multiple Account records in bulk using data from a SQL Server database. This example demonstrates using the <code>SOURCE</code> clause with <code>UPSERT INTO</code> to synchronize data from an external source to Zoho CRM.</p> 

UPSERT INTO Accounts
SOURCE(
  'MSSQL' --ODBC or OLEDB
  ,'Data Source=localhost;Initial Catalog=Test;Integrated Security=true'
  ,'select ''Test Account-A'' as Account_Name,''111-111-1111'' as Phone
    UNION
    select ''Test Account-B'' as Account_Name,''222-222-2222'' as Phone
   '
)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read accounts

<p>Reads all records from the <code>Accounts</code> table. This example demonstrates a basic SELECT query to retrieve a complete list of accounts from Zoho CRM.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Update record owner

<p>Updates the owner of a record (e.g., Account, Contact, Deal, Lead). This example shows how to update a lookup field like <code>Owner</code> using a unique identifier such as email or ID. In this case, the owner of a specific Account is updated by email.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Accounts 
SET Owner=''{email: "bob-the-salesman@abc.com"}''
--SET Owner=''{id: "1558554000186378001"}'' --you can also use Id
Where Id=''1558554000137221573''';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Update deal account or contact

<p>Updates the Account and Contact lookup fields for a Deal record. This example demonstrates setting the <code>Account_Name</code> by name and <code>Contact_Name</code> by ID for a specific Deal.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Deals 
SET Account_Name=''{"name": "Company ABCD"}'',    --by name or id 
    Contact_Name=''{"id": "1558554000186378001"}''  --by id 
Where Id=''1558554000137221573''';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Create a deal with lookup fields

<p>Creates a new Deal record with lookup fields populated. This example demonstrates how to set <code>Account_Name</code>, <code>Contact_Name</code>, and <code>Owner</code> using their respective Names, IDs, or Emails during the INSERT operation.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Deals(
	Deal_name,
	Amount,
	Lead_Source,
	Account_Name,
	Contact_Name,
	Owner
	)
VALUES
   (
   ''My Test Deal Creatyed on <<FUN_NOW>>'', --deal name
   1000.50, --amount
   ''Cold Call'', --lead source
   ''{name:"ZappySys"}'', --account name or use id ''{id:"1558554000030180013"}''
   ''{id:"1558554000089352998"}'', --contact id 
   ''{id:"1558554000089352912"}'' --owner id or use email {email: "bob-the-salesman@abc.com"}
   )';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Read an account by ID

<p>Reads a single Account record by its ID. This example demonstrates filtering the <code>Accounts</code> table using the <code>WHERE</code> clause with a specific ID.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts Where Id=1558554000105110008';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Read accounts modified after date

<p>Reads Account records modified after a specified date and time. This example demonstrates using the <code>ModifiedSince</code> parameter in the <code>WITH</code> clause to perform an incremental fetch.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts WITH(ModifiedSince = ''2020-01-07T00:00:00'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Update a lead

<p>Updates a specific Lead record. This example demonstrates modifying fields like <code>Designation</code> and <code>Company</code> for a Lead identified by its ID in the <code>WHERE</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Leads SET Designation=''VP Sales'', Company=''Test'' Where id=1558554000012181009';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Update a lead (legacy)

<p>Updates a specific Lead record using an alternative syntax. This example demonstrates passing the <code>id</code> as a column in the <code>SET</code> clause instead of using a <code>WHERE</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Leads SET id=''1558554000012181009'' /* id must be supplied */, Designation=''VP Sales'', Company=''Test''';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Read accounts modified after date (filtered)

<p>Reads Account records matching a name pattern and modified after a specific date. This example combines a <code>WHERE</code> clause filter with the <code>ModifiedSince</code> parameter for targeted data retrieval.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Accounts Where Account_Name LIKE ''Test%'' WITH(ModifiedSince = ''2020-01-07T00:00:00'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Search accounts by criteria

<p>Searches for Account records using specific criteria. This example demonstrates using the <code>search_Accounts</code> endpoint with the <code>criteria</code> parameter to perform a server-side search (e.g., accounts starting with "test").</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM search_Accounts WITH(criteria=''Account_Name:starts_with:test'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Create an account

<p>Creates a new Account record. This example demonstrates using the <code>INSERT INTO</code> statement to add a new account with <code>Account_Name</code> and <code>Phone</code>.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Accounts(Account_Name, Phone) VALUES(''Company ABC'',''111-567-8888'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Create an account (with output)

<p>Creates a new Account record and returns the created record's details. This example uses <code>WITH(Output=1)</code> to display the result of the INSERT operation.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Accounts(Account_Name, Phone) VALUES(''Company ABC'',''111-567-8888'') WITH(Output=1)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Delete an account

<p>Deletes a single Account record by its ID. This example demonstrates using the <code>DELETE FROM</code> statement with a <code>WHERE</code> clause specifying the record ID.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Accounts WHERE id=11111111111';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Delete accounts in bulk

<p>Deletes multiple Account records by specifying a list of IDs. This example demonstrates passing a comma-separated list of IDs to the <code>Id</code> parameter in the <code>WITH</code> clause (up to 100 IDs).</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Accounts WITH(Id=''11111,22222,33333'')';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Upsert an account

<p>Updates or inserts an Account record. This example demonstrates using the <code>UPSERT INTO</code> statement, which checks for existing records based on unique fields before deciding to update or insert.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO Accounts(Account_Name, Phone) VALUES(''Company ABC'',''111-567-8888'') WITH(Output=1)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Upsert a lead

<p>Updates or inserts a Lead record. This example demonstrates using the <code>UPSERT INTO</code> statement for Leads, updating based on unique fields like Email.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO Leads(Last_Name, Email) VALUES(''Patel'',''zpatel@abc.com'') WITH(Output=1)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Bulk create accounts using SQL Server data

<p>Creates multiple Account records in bulk using data from a SQL Server database. This example demonstrates using the <code>SOURCE</code> clause to read data from an external ODBC/OLEDB source and insert it into Zoho CRM.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Accounts
SOURCE(
  ''MSSQL'' --ODBC or OLEDB
  ,''Data Source=localhost;Initial Catalog=Test;Integrated Security=true''
  ,''select ''''Test Account-A'''' as Account_Name,''''111-111-1111'''' as Phone
    UNION
    select ''''Test Account-B'''' as Account_Name,''''222-222-2222'''' as Phone
   ''
)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Bulk upsert accounts using SQL Server data

<p>Upserts multiple Account records in bulk using data from a SQL Server database. This example demonstrates using the <code>SOURCE</code> clause with <code>UPSERT INTO</code> to synchronize data from an external source to Zoho CRM.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO Accounts
SOURCE(
  ''MSSQL'' --ODBC or OLEDB
  ,''Data Source=localhost;Initial Catalog=Test;Integrated Security=true''
  ,''select ''''Test Account-A'''' as Account_Name,''''111-111-1111'''' as Phone
    UNION
    select ''''Test Account-B'''' as Account_Name,''''222-222-2222'''' as Phone
   ''
)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];