Upsert Organization - Create or Update
Name
upsert_organization
Description
Create or Update an organization in (Upsert) [API reference]
Related Tables
Parameters
Parameter | Label | Required | Options | Description | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
There are no parameters |
Output Columns
Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | Description |
---|---|---|---|---|---|
id |
DT_I8
|
bigint
|
False |
||
name |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
notes |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
created_at |
DT_DBTIMESTAMP
|
datetime
|
False |
||
details |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
domain_names |
DT_WSTR
|
nvarchar(144)
|
144 | False |
|
domain_names_1 |
DT_WSTR
|
nvarchar(255)
|
255 | False |
|
domain_names_2 |
DT_WSTR
|
nvarchar(255)
|
255 | False |
|
domain_names_3 |
DT_WSTR
|
nvarchar(255)
|
255 | False |
|
external_id |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
group_id |
DT_I8
|
bigint
|
False |
||
shared_comments |
DT_BOOL
|
bit
|
False |
||
shared_tickets |
DT_BOOL
|
bit
|
False |
||
tags |
DT_WSTR
|
nvarchar(144)
|
144 | False |
|
tags_1 |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
tags_2 |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
tags_3 |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
tags_4 |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
tags_5 |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
updated_at |
DT_DBTIMESTAMP
|
datetime
|
False |
||
url |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
organization_field_dt_[$parent.key$] |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
organization_field_[$parent.key$] |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
Input Columns
Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | Description |
---|---|---|---|---|---|
id |
DT_I8
|
bigint
|
False |
||
name |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
notes |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
external_id |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
group_id |
DT_I8
|
bigint
|
False |
||
details |
DT_WSTR
|
nvarchar(4000)
|
4000 | False |
|
tags |
DT_WSTR
|
nvarchar(4000)
|
4000 | True |
|
organization_fields |
DT_WSTR
|
nvarchar(4000)
|
4000 | True |
|
domain_names |
DT_WSTR
|
nvarchar(1000)
|
1000 | True |
|
shared_comments |
DT_BOOL
|
bit
|
False |
||
shared_tickets |
DT_BOOL
|
bit
|
False |
Examples
SSIS
Use Zendesk Connector in API Source component to read data or in API Destination component to read/write data:
Upsert rows into Organizations table using API Destination
This Endpoint belongs to Organizations table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

ODBC application
Use these SQL queries in your ODBC application data source:
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.
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'
)
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.
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
')
upsert_organization
endpoint belongs to
Organizations
table(s), and can therefore be used via those table(s).
SQL Server
Use these SQL queries in SQL Server after you create a data source in Data Gateway:
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];
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];
upsert_organization
endpoint belongs to
Organizations
table(s), and can therefore be used via those table(s).