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_table_rows
INSERT		insert_table_row
UPDATE		update_table_row
UPSERT
DELETE		delete_table_row
LOOKUP		get_table_rows

Examples

SSIS

Use ServiceNow 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 - ServiceNow

Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access.

ServiceNow

[Dynamic Table]

Required Parameters
TableName	Fill-in the parameter...
Optional Parameters
SysId
Fields
Fields to include in response (Keep blank to get all fields -OR- Enter comma separated list)
Query / Order By (Server Side Filter)
ContineOn404Error	True
DateFormatString	yyyy-MM-dd HH:mm:ss

SSIS API Source - Read from table or endpoint

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

API Destination - ServiceNow

Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access.

ServiceNow

[Dynamic Table]

Select

Required Parameters
TableName	Fill-in the parameter...
Optional Parameters
SysId
Fields
Fields to include in response (Keep blank to get all fields -OR- Enter comma separated list)
Query / Order By (Server Side Filter)
ContineOn404Error	True
DateFormatString	yyyy-MM-dd HH:mm:ss

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Get a list of incidents

SELECT * FROM incident 

--Using Primary Key				
--SELECT * FROM any_table_here WHERE sys_id='109562a3c611227500a7b7ff98cc0dc7' --Primary Key in WHERE clause

--Using Filter Expression
--SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition
--SELECT * FROM incident WITH(Query='number!=INC0000001') --Not equal condition
--SELECT * FROM incident WITH(Query='numberININC0000001,INC0000002,INC0000003') --IN condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7') --AND condition
--SELECT * FROM incident WITH(Query='number=INC0000001^ORnumber=INC0000002') --OR condition
--SELECT * FROM incident WITH(Query='numberLIKE0001') --LIKE condition
--SELECT * FROM incident WITH(Query='numberSTARTSWITHINC00') --StartWith condition
--SELECT * FROM incident WITH(Query='numberENDSWITH0001') --StartWith condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7^NQORnumber=INC0000002') --AND / OR MIXED using NQ (NewQuery Operator)  -- (number=INC0000001 and state=7) OR (number=INC0000002)

--more information about filter here https://docs.servicenow.com/bundle/utah-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html


-- To read all available tables execute this query:
-- SELECT * FROM Tables

-- Other common tables:
-----------------------
-- SELECT * FROM sys_db_object
-- SELECT * FROM sys_dictionary
-- SELECT * FROM sys_user
-- SELECT * FROM sys_user_has_role
-- SELECT * FROM sys_user_grmember
-- SELECT * FROM task
-- SELECT * FROM task_sla
-- SELECT * FROM incident
-- SELECT * FROM incident_sla
-- SELECT * FROM change_request
-- SELECT * FROM cmdb_ci_computer
-- SELECT * FROM cmdb_ci_outage
-- SELECT * FROM cmdb_ci
-- SELECT * FROM sn_customerservice_case
-- SELECT * FROM kb_knowledge
-- SELECT * FROM kb_use
-- SELECT * FROM sc_req_item
-- SELECT * FROM sc_request
-- SELECT * FROM sc_task

Get an incident by sys_id

SELECT * FROM incident
WHERE sys_id = 'SYS_ID_GOES_HERE'

Get only selected columns and speed up the query

By default all column values are returned in the result set. However, you can instruct the ServiceNow API to send only specific columns you care about by setting 'Fields' parameter in the WITH clause. This will speed up the response significantly.

SELECT number,name FROM incident WITH (Fields='number,name') --Setting Fields parameter will speed up the query

Query table using serverside filter expression

This example shows how to filter data from a table using server side filter expression. Its always faster to do this way rather than using in WHERE clause (Client Side Filter)

SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition

--syntax (single condition)
--sysparm_query=<col_name><operator><value>

--syntax (multiple conditions)
--sysparm_query=<col_name><operator><value>[<operator><col_name><operator><value>]

--more info check below links
--  https://docs.servicenow.com/bundle/rome-application-development/page/integrate/inbound-rest/concept/c_TableAPI.html#title_table-GET
--  https://docs.servicenow.com/bundle/utah-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html
--  Some More Advanced Params listed here
--  https://developer.servicenow.com/dev.do#!/learn/learning-plans/xanadu/servicenow_application_developer/app_store_learnv2_rest_xanadu_request_parameters  

--More examples				
--SELECT * FROM any_table_here WHERE sys_id='109562a3c611227500a7b7ff98cc0dc7' --Primary Key in WHERE clause
--SELECT * FROM incident WITH(Query='number=INC0000001') --Equal condition
--SELECT * FROM incident WITH(Query='number!=INC0000001') --Not equal condition
--SELECT * FROM incident WITH(Query='numberININC0000001,INC0000002,INC0000003') --IN condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7') --AND condition
--SELECT * FROM incident WITH(Query='number=INC0000001^ORnumber=INC0000002') --OR condition
--SELECT * FROM incident WITH(Query='numberLIKE0001') --LIKE condition
--SELECT * FROM incident WITH(Query='numberSTARTSWITHINC00') --StartWith condition
--SELECT * FROM incident WITH(Query='numberENDSWITH0001') --StartWith condition
--SELECT * FROM incident WITH(Query='number=INC0000001^state=7^NQORnumber=INC0000002') --AND / OR MIXED using NQ (NewQuery Operator)  -- (number=INC0000001 and state=7) OR (number=INC0000002)

Create an incident

INSERT INTO incident(incident_state, severity, category, cause)
VALUES (3, 1, 'software', 'Missing a software feature.')

Update an incident

UPDATE incident
SET cause = 'A bug in the software'
   ,severity = 3
   ,incident_state = 2
WHERE sys_id = 'SYS_ID_GOES_HERE'

Delete an incident

DELETE incident
WHERE sys_id = 'SYS_ID_GOES_HERE'

Get a list of system tables

SELECT * FROM SystemTables

Get a list of user tables

SELECT * FROM UserTables

SQL Server

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