Zendesk Connector

Documentation

Version: 9

Documentation

SQL Server guide

SQL examples for SQL Server

The ZappySys API Driver is a user-friendly interface designed to facilitate the seamless integration of various applications with the Zendesk API. With its intuitive design and robust functionality, the ZappySys API Driver simplifies the process of configuring specific API endpoints to efficiently read or write data from Zendesk.

On this page you will find some SQL examples which can be used for API ODBC Driver or Data Gateway API Connector.

Get all Tickets

Read all tickets from Zendesk

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Tickets';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get Ticket information by ID(s)

Read multiple ticket information from Zendesk by ID(s)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from get_tickets_by_ids
WITH(ticket_ids=''104861,104860'')';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get Single Ticket information by ID

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from Tickets
WHERE ID=104861';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get all tickets (incremental) modified after ceratin date/time

Read tickets modified after ceratin date/time. This is incremental data fetch. If you use this endpoint to read data and insert to destination like SSIS Upsert Destination (ZappySys Product) then make sure you do not select delete option in the target. Only choose Update + Insert for Incremental fetch.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_tickets_incr
WITH(
	    start_time=''2012-01-31T00:00:00'' --modified after exact date / time (yyyy-MM-dd or yyyy-MM-ddTHH:mm:ss)
		
	  --start_time=''yesterday'' --modified after yesterday
	  --start_time=''now-5h'' --modified after current time minus 5 hours
	  --start_time=''today-60s'' --modified after today minus 60 seconds
	  --start_time=''weekstart'' --modified after weekstart
	  --start_time=''monthstart-1d'' --modified after month start minus 1 day
	  --start_time=''yearstart-1d'' --modified after year start minus 1 day
	  --start_time=''yearstart+1d'' --modified after year start plus 1 day
	  --start_time=''yearend+1d'' --modified after year end plus 1 day
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get all users (incremental) modified after ceratin date/time

Read users modified after ceratin date/time. This is incremental data fetch. If you use this endpoint to read data and insert to destination like SSIS Upsert Destination (ZappySys Product) then make sure you do not select delete option in the target. Only choose Update + Insert for Incremental fetch.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_users_incr
WITH(
	    start_time=''2012-01-31T00:00:00'' --modified after exact date / time (yyyy-MM-dd or yyyy-MM-ddTHH:mm:ss)
		
	  --start_time=''yesterday'' --modified after yesterday
	  --start_time=''now-5h'' --modified after current time minus 5 hours
	  --start_time=''today-60s'' --modified after today minus 60 seconds
	  --start_time=''weekstart'' --modified after weekstart
	  --start_time=''monthstart-1d'' --modified after month start minus 1 day
	  --start_time=''yearstart-1d'' --modified after year start minus 1 day
	  --start_time=''yearstart+1d'' --modified after year start plus 1 day
	  --start_time=''yearend+1d'' --modified after year end plus 1 day
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get all organizations (incremental) modified after ceratin date/time

Read organizations modified after ceratin date/time. This is incremental data fetch. If you use this endpoint to read data and insert to destination like SSIS Upsert Destination (ZappySys Product) then make sure you do not select delete option in the target. Only choose Update + Insert for Incremental fetch.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_organizations_incr
WITH(
	    start_time=''2012-01-31T00:00:00'' --modified after exact date / time (yyyy-MM-dd or yyyy-MM-ddTHH:mm:ss)
		
	  --start_time=''yesterday'' --modified after yesterday
	  --start_time=''now-5h'' --modified after current time minus 5 hours
	  --start_time=''today-60s'' --modified after today minus 60 seconds
	  --start_time=''weekstart'' --modified after weekstart
	  --start_time=''monthstart-1d'' --modified after month start minus 1 day
	  --start_time=''yearstart-1d'' --modified after year start minus 1 day
	  --start_time=''yearstart+1d'' --modified after year start plus 1 day
	  --start_time=''yearend+1d'' --modified after year end plus 1 day
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get all tickets for specific organization

Read tickets for a specified organization_id (company).

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_tickets_by_org
WITH(
	  organization_id=''12''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get all tickets for a specific user who requested ticket(s)

Read tickets for a specified user_id who is listed as requested by.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_tickets_for_user_requested
WITH(
	  user_id=''123''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get all tickets for a specific user who is CCed on ticket(s)

Read tickets for a specified user_id who is listed as CC.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_tickets_for_user_cced
WITH(
	  user_id=''123''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get all tickets for a specific user who is assigned to ticket(s)

Read tickets for a specified user_id who is assigned to ticket(s).

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_tickets_for_user_assigned
WITH(
	  user_id=''123''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get total tickets count

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_tickets_count';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get total tickets count for a specific organization

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_tickets_count_org
WITH(
	  organization_id=''123''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get all attachment urls for a specific ticket

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_ticket_attachments
WITH(
	  ticket_id=''104351''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get User(s)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Users --Where Id=1234';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get multiple users by ID(s)

Read multiple user information from Zendesk by ID(s)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from get_users_by_ids
WITH(user_ids=''1111,2222,3333'')';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get Organization(s)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Organizations --Where Id=1234';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Get multiple organizations by ID(s)

Read multiple organization information from Zendesk by ID(s)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * from get_organizations_by_ids
WITH(organization_ids=''1111,2222,3333'')';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Create a new ticket

Use Tickets_Bulk instead of Tickets table if you like to multiple tickets at once. Tickets_Bulk table supports BULK operation (using SOURCE clause).

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Tickets(
	subject
	,status
	,assignee_id
	,comment_body_html --(for html body)
	--,comment_body (for plain text)
	,comment_public
	,tags
	,custom_fields)
VALUES(
  ''Test Ticket Subject - From email''
, ''new'' --new, solved, closed
, 18590685428 --assign to agent id
, ''This is <b>html body</b>'' --markup also supported
, 1 --1=public, 0=private 
, ''["tag1","tag2"]'' 
  --below json can be obtained using select custom_fields from tickets where id=1234
, ''[
  {
    "id": 56608448,
    "value": "1122"
  },
  {
    "id": 57385967,
    "value": "ORD-12345"
  }
]''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Create a new ticket (behalf of user)

This example creates ticket behalf of user (i.e. requester_email and requester_name) and if user is missing then creates a new user for supplied username and email. Use Tickets_Bulk instead of Tickets table if you like to multiple tickets at once. Tickets_Bulk table supports BULK operation (using SOURCE clause).

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Tickets(
	subject
	,status
	,requester_email
	,requester_name
	,assignee_id 
	--,group_id --or assign to group
	,comment_body_html --(for html body)
	--,comment_body (for plain text)
	,comment_public
	,tags
	,custom_fields)
VALUES(
  ''Test Ticket Subject - From email''
, ''new'' --new, solved, closed
, ''fromsomeuser@abc.com'' --from email
, ''Bob Smith'' --submitter name needed if its new user
, 18590685428 --assign to agent id
--,123435454 --or use group id if not assignee 
, ''This is <b>html body</b>'' --markup also supported
--, ''This is plain text''
, 1 --1=public, 0=private 
, ''["tag1","tag2"]'' 
  --below json can be obtained using select custom_fields from tickets where id=1234
, ''[
  {
    "id": 56608448,
    "value": "1122"
  },
  {
    "id": 57385967,
    "value": "ORD-12345"
  }
]''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

BULK Create tickets (BULK read / write from external source)

This examples shows how to use SOURCE clause to read data from MS SQL Server (or other external system) and send data to Zendesk using Bulk API

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Tickets_BULK 
SOURCE(
  ''MSSQL'' --ODBC or OLEDB
  ,''Data Source=localhost;Initial Catalog=Test;Integrated Security=true''
  ,''select ''''Test Subject#1'''' as subject,''''new'''' as status,''''this is description#1'''' as comment_body from Projects''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Delete a ticket by Id

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Tickets WHERE Id=111';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Delete multiple by tickets by Id list - BULK (comma seperated - max 100 ids)

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Tickets_Bulk WHERE [$$ticket_ids]=''111,222,333''';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Update ticket by Id

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Tickets 
SET subject=''Test ticket subject''
  , tags=''["tag1","tag2"]'' 
  --, additional_tags =''["tag1","tag2"]'' --tags you like to remove
  --, remove tags=''["tag1","tag2"]''
  , status=''pending'' --new, solved, closed
  --below json can be obtained using select custom_fields from tickets where id=1234
  , custom_fields=''[
  {
    "id": 10000,
    "value": "some value for prop1"
  },
  {
    "id": 10001,
    "value": "some value for prop2"
  }
]''
Where Id = 1234';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Update exising ticket with private comment

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Tickets 
SET comment_body=''Closing this ticket''
  , comment_public = 0 --or 1
  , status=''solved'' --pending, new, closed
  --, comment_body_html=''Closing <b>this</b> ticket''
  --, comment_author_id=123545   
Where Id = 1234';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Update exising ticket with HTML body

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Tickets 
SET comment_body_html=''Closing <b>this</b> ticket''
  , status=''solved'' --pending, new, closed
  --, comment_author_id=123545   
  --, comment_public = 0 --or 1  
Where Id = 1234';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Update ticket with Add or remove tags

Update ticket with Add new tags or remove tags. Adding / removing tags available via special field names only in BULK mode so you must use Tickets_BULK table.

DECLARE @MyQuery NVARCHAR(MAX) = 'Update Tickets_Bulk 
SET subject=''New subject''
  , additional_tags =''["new-tag1","new-tag2"]'' --//add some tags
  --, remove_tags=''["old-tag1","old-tag2"]'' --//removes some tags
  --, tags=''["tag1","tag2"]'' --//overwrite all tags
  , status=''pending'' --new, solved, closed
Where Id = 1234';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Update multiple tickets - (BULK read / write from external source)

This examples shows how to perform bulk update using SOURCE clause. It reads data from external system (in this example MS SQL Server) and sends data to Zendesk using Bulk API

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Tickets_BULK 
SOURCE(
  ''MSSQL'' --ODBC or OLEDB
  ,''Data Source=localhost;Initial Catalog=Test;Integrated Security=true''
  ,''select 104897 as Id, ''''Test Subject#1-updated-bulk-demo'''' as subject,''''new'''' as status
    UNION 
    select 104898 as Id, ''''Test Subject#2-updated-bulk-demo'''' as subject,''''new'''' as status''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Delete multiple tickets - (BULK read / write from external source)

This examples shows how to perform bulk delete using SOURCE clause. It reads data from external system (in this example MS SQL Server) and sends data to Zendesk using Bulk API

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Tickets_BULK 
SOURCE(
  ''MSSQL'' --ODBC or OLEDB
  ,''Data Source=localhost;Initial Catalog=Test;Integrated Security=true''
  ,''select 104897 as Id 
    UNION 
    select 104898 as Id''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Create a new ticket using Raw JSON Body approach

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Tickets(_rawdoc_) 
VALUES(
  ''{
      "subject": "Test ticket #1",    
      "comment": {
        "body": "Test ticket #1"
      },
      "priority": "urgent"
    }''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Update an existing ticket using Raw JSON Body approach

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Tickets
SET _rawdoc_ =
  ''{
      "subject": "Test ticket #1",    
      "comment": {
        "body": "Test ticket #1"
      },
      "priority": "urgent"
    }''
WHERE Id=1234';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Create multiple ticket(s) using a RAW JSON body (single or multiple)

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Tickets_Bulk(_rawdoc_) 
VALUES(
  ''[
    {
      "subject": "Test ticket #1",    
      "comment": {
        "body": "Test ticket #1"
      },
      "priority": "urgent"
    },
    {
      "subject": "Test ticket #2",    
      "comment": {
        "body": "This is a comment for #2"
      },
      "priority": "normal"
    }
  ]''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Create User

Create a new user for specified email id and other information. If user with same email exists then call fails.

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Users
(
 name
,email
,organization_id
,phone
,default_group_id
,locale
,moderator
,skip_verify_email
,only_private_comments
,signature
,tags
,time_zone
,role
,external_id
,alias
,details
,notes
,remote_photo_url
,user_fields
)
VALUES(
   ''Bob Walton''
  ,''bob@abc.com''
  ,5397098432795
  ,''111-222-3333''
  ,114094762733
  ,''en-US''
  ,''false''
  ,''true'' --true=do not send verify account email
  ,1 --user can put only private comments
  ,''Best regards, Support Team'' --Only agents and admins can have signatures
  ,''["paid","trial","solved"]'' 
  ,''America/New_York''
  ,''end-user'' --agent or admin 
  ,''zcrm_1558554000052161270''
  ,''some alias''
  ,''some details''
  ,''some notes''
  ,''https://zappysys.com/wp-content/uploads/2021/10/Slider-API-Hub-1.png'' --this does not work in INSERT (works only in UPDATE/UPSERT for now)
  ,''{"birthdate": "1981-01-23", "gender": "M"}''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Update User

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Users
SET name=''Sam Walton''
  ,email=''sam@abc.com''
  ,organization_id=5397098432795
  ,phone=''111-222-3333''
  ,default_group_id=114094762733
  ,locale=''en-US''
  ,skip_verify_email=''true'' --do not send verify account email
  ,moderator=''false''
  ,only_private_comments=1 --user can put only private comments
  ,signature=''Best regards, Support Team'' --Only agents and admins can have signatures
  ,tags=''["paid","trial","solved"]'' 
  ,time_zone=''America/New_York''
  ,role=''end-user''
  ,external_id=''zcrm_1558554000052161269''
  ,alias=''some alias''
  ,details=''some details''
  ,notes=''some notes''
  ,remote_photo_url=''https://zappysys.com/wp-content/uploads/2021/10/Slider-API-Hub-1.png''
  ,user_fields=''{"birthdate": "1981-01-23", "gender": "M"}''
Where id=21811221397915';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Upsert User (Create or Update)

Creates a user if the user does not already exist, or updates an existing user identified by e-mail address or external ID.

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Users
(
 name
,email
,organization_id
,phone
,default_group_id
,locale
,moderator
,skip_verify_email
,only_private_comments
,signature
,tags
,time_zone
,role
,external_id
,alias
,details
,notes
,remote_photo_url
,user_fields
)
VALUES(
   ''Bob Walton''
  ,''bob@abc.com''
  ,5397098432795
  ,''111-222-3333''
  ,114094762733
  ,''en-US''
  ,''false''
  ,''true'' --true=do not send verify account email
  ,1 --user can put only private comments
  ,''Best regards, Support Team'' --Only agents and admins can have signatures
  ,''["paid","trial","solved"]'' 
  ,''America/New_York''
  ,''end-user'' --agent or admin 
  ,''zcrm_1558554000052161270''
  ,''some alias''
  ,''some details''
  ,''some notes''
  ,''https://zappysys.com/wp-content/uploads/2021/10/Slider-API-Hub-1.png'' --this does not work in INSERT (works only in UPDATE/UPSERT for now)
  ,''{"birthdate": "1981-01-23", "gender": "M"}''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Delete User

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Users Where id=21855694556443 --it doesnt delete user from system but only changes active flag';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Search for user with email id

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_search
WITH(
	  query=''type:user email:bob@abc.com''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Create Organization

Create a new organization for specified email id and other information. If organization with same email exists then call fails.

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Organizations
(
	 name
	,external_id
	,group_id 
	,tags
	,details
	,notes
	,organization_fields
	,domain_names
	,shared_tickets
	,shared_comments
)
VALUES(
	''Abc Inc''
	,''zcrm_1558554000052161270''  --external_id
	,114094762733  
	,''["paid","trial","solved"]'' 
	,''some details''
	,''some notes''
	,''{"startdate": "1981-01-23", "revenue": 12000000.50, "somenumber": 1235678}''
	,''["aaa.com", "bbb.com"]''
	,''false''
	,''false''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Update Organization

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Organizations
SET  name=''Abc Inc''
	,external_id=''zcrm_1558554000052161270''
	,group_id=114094762733 
	,tags=''["paid","trial","solved"]''
	,details=''some details''
	,notes=''some notes''
	,organization_fields=''{"startdate": "1981-01-23", "revenue": 12000000.50, "somenumber": 1235678}''
	,domain_names=''["aaa.com", "bbb.com"]''
	,shared_tickets=''false''
	,shared_comments=''false''
Where id=21863188631451';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Upsert Organization (Create or Update)

Creates an organization if the organization does not already exist, or updates an existing organization identified by e-mail address or external ID.

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO Organizations
(
	 name
	--id or external_id can be supplied for UPSERT
	,id 
	--or--
	,external_id
	 
	,group_id 
	,tags
	,details
	,notes
	,organization_fields
	,domain_names
	,shared_tickets
	,shared_comments
)
VALUES(
	''Abc Inc''
	,1234567 --id
	--or--
	,''zcrm_1558554000052161270''  --external_id
	
	,114094762733  
	,''["paid","trial","solved"]'' 
	,''some details''
	,''some notes''
	,''{"startdate": "1981-01-23", "revenue": 12000000.50, "somenumber": 1235678}''
	,''["aaa.com", "bbb.com"]''
	,''false''
	,''false''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Delete Organization

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Organizations Where id=21855694556443';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Search records using query expression (tickets, organization, users)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_search --limited to 1000 rows max
--OR
--FROM get_search_export --returns more than 1000 rows but no sorting or extra column allowed
WITH(
       filtertype='''', --can be ticket, organization, user, group. If you set here then no need to put in query (e.g. query=''type:ticket ...''). This option is ignored for get_search_Export.
	  ,sort_by=''updated_at'' --created_at, status, priority, ticket_type. This option is ignored for get_search_Export.
	  ,sort_order=''asc''  --desc (not valid for get_search_export). This option is ignored for get_search_Export.

    --read this for more information 
	--https://support.zendesk.com/hc/en-us/articles/4408883318554-Searching-users-groups-and-organizations
	--https://support.zendesk.com/hc/en-us/articles/4408886879258-Zendesk-Support-search-reference#topic_crj_yev_uc
	--https://support.zendesk.com/hc/en-us/articles/4408882086298-Searching-tickets
	
      ,query=''type:ticket subject:"Upsert Destination"''   --search tickets where subject contains phrase "Upsert Destination" (special characters like comma / dash etc ignored in match)
	--,query=''type:ticket 519872''   --search ticket with id 519872
	--,query=''type:ticket tags:solved,odbc_powerpack updated>1months'' --search tickets where two tags set solved,odbc_powerpack and updated in last one month
	--,query=''type:ticket -tags:odbc_powerpack updated>1months'' --search tickets where where tags not like odbc_powerpack and updated in last one month
	--,query=''type:ticket status<solved created>1months'' --search tickets with status not solved and created in last one month
	--,query=''type:ticket created:2014-08-01'' --search tickets created on 2014-08-01 (UTC)
	--,query=''type:ticket created>2014-08-01T10:30:00Z created<2014-08-01T12:00:00Z'' --search tickets created between two datetime (UTC time)
	--,query=''type:ticket requester:amy@mondocam.com'' --search tickets where requester is amy@mondocam.com
	--,query=''type:ticket description:defective'' --search tickets where decription contains word "defective"
	--,query=''type:ticket description:"product is defective"'' --search tickets where decription contains phrase  "product is defective"
	--,query=''type:ticket priority>low'' --search tickets where priority greater than low (e.g. medium or high)"
	
	--,query=''ZappySys'' --phrase match (no type): anywhere in ticket / organization / user contains ZappySys word - If filtertype parameter supplied then only those type included
	--,query=''ZappySys*'' --phrase match (no type): wildcard usage
	--,query=''type:user "ZappySys"'' --search users with term "ZappySys" anywhere in text properties
    --,query=''type:user email:"@zappysys"'' --search users with term "@ZappySys.com" in email field
	--,query=''type:user name:"zappysys"'' --search users with term "ZappySys" in name field
	--,query=''type:user details:"zappysys"'' --search users with term "ZappySys" in details field
    --,query=''type:organization "ZappySys LLC"'' --phrase match: organization name contains ZappySys LLC
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Bulk Import Mode for Users or Organizations

This example shows how to upsert in bulk mode from external data and import into Zendesk. We used Microsoft SQL Server as source system in below example with static value query (just one row) but it can be any number of rows.

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO Organizations
SOURCE(''MSSQL'' --ODBC
, ''Data Source=localhost;Initial Catalog=test;Integrated Security=true''

--For bulk input, map columns in External Query (Must use alias column name to match INSERT command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--''ODBC'', ''Driver={ZappySys CSV Driver};DataPath=C:\AccountsToInsert.csv''
--''ODBC'', ''DSN=MyDSN''
--''OLEDB'', ''Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;''

, ''select
      21863188631451 as id
    , ''''Abc Inc'''' as name
	,''''zcrm_1558554000052161270'''' as external_id
	,114094762733  as group_id
	,''''["paid","trial","solved"]'''' as tags
	,''''some details'''' as details
	,''''some notes'''' as notes
	,''''{"startdate": "1981-01-23", "revenue": 12000000.50, "somenumber": 1235678}'''' as organization_fields
	,''''["aaa.com", "bbb.com"]'''' domain_names
	,''''false'''' as shared_tickets
	,''''false'''' as shared_comments
'')';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Learn more about this SQL query.

Getting Started with Examples

ZappySys API Driver is a powerful software solution designed to facilitate the extraction and integration of data from a wide range of sources through APIs. Its intuitive design and extensive feature set make it an essential asset for any organization dealing with complex data integration tasks.

To get started with examples using ZappySys API Driver, please click on the following applications:

		Connect Zendesk in SQL Server
		Connect Zendesk in Power BI
		Connect Zendesk in SSRS
		Connect Zendesk in Informatica
		Connect Zendesk in MS Access
		Connect Zendesk in MS Excel
		Connect Zendesk in SSAS
		Connect Zendesk in C#
		Connect Zendesk in Python
		Connect Zendesk in JAVA
		Connect Zendesk in Tableau
		Connect Zendesk in SAP Crystal Reports
		Connect Zendesk in Azure Data Factory (Pipeline)
		Connect Zendesk in Talend Studio
		Connect Zendesk in UiPath
		Connect Zendesk in PowerShell
		Connect Zendesk in ODBC

Key features of the ZappySys API Driver include:

The API ODBC driver facilitates the reading and writing of data from numerous popular online services (refer to the complete list here) using familiar SQL language without learning complexity of REST API calls. The driver allows querying nested structure and output as a flat table. You can also create your own ODBC / Data Gateway API connector file and use it with this driver.

Intuitive Configuration: The interface is designed to be user-friendly, enabling users to easily set up the specific API endpoints within Zendesk without requiring extensive technical expertise or programming knowledge.
Customizable Endpoint Setup: Users can conveniently configure the API endpoint settings, including the HTTP request method, endpoint URL, and any necessary parameters, to precisely target the desired data within Zendesk.
Data Manipulation Capabilities: The ZappySys API Driver allows for seamless data retrieval and writing, enabling users to fetch data from Zendesk and perform various data manipulation operations as needed, all through an intuitive and straightforward interface.
Secure Authentication Integration: The driver provides secure authentication integration, allowing users to securely connect to the Zendesk API by inputting the necessary authentication credentials, such as API tokens or other authentication keys.
Error Handling Support: The interface is equipped with comprehensive error handling support, ensuring that any errors or exceptions encountered during the data retrieval or writing process are efficiently managed and appropriately communicated to users for prompt resolution.
Data Visualization and Reporting: The ZappySys API Driver facilitates the seamless processing and presentation of the retrieved data from Zendesk, enabling users to generate comprehensive reports and visualizations for further analysis and decision-making purposes.

Overall, the ZappySys API Driver serves as a powerful tool for streamlining the integration of applications with Zendesk, providing users with a convenient and efficient way to access and manage data, all through a user-friendly and intuitive interface.