Read incidents incrementally (by updated date)

Fetch incidents updated after a specific date. This pattern is recommended for incremental loads and scheduled sync jobs.

Use this approach to avoid full table scans, reduce API calls, and improve overall performance.

The date filter can be specified using either a static date value or a dynamic date expression. Dynamic date expressions allow relative date calculations and are useful for automated jobs.

Examples of dynamic date expressions:

• yesterday – incidents updated yesterday
• today – incidents updated today
• today-1d – incidents updated in the last 24 hours
• monthstart – incidents updated since the beginning of the month
• monthend-1d – incidents updated up to the last day of the previous month

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

-- Incrementally fetch incidents updated after a given date
SELECT *
FROM get_table_rows
WITH(
TableName='incident',

-- Load only incidents updated in the last 24 hours
Query='sys_updated_on>=<<today-1d,FUN_TO_DATETIME>>'

--OR Updated after static date time
--Query='sys_updated_on>=2025-01-01T23:23:59'
--Query='sys_updated_on>=2025-01-01'

--Other examples of placeholder function usage
--Query='sys_updated_on>=<<monthstart-7d,FUN_TO_DATETIME>>'
--Query='sys_updated_on>=<<monthstart-5d+1y,FUN_TO_DATETIME>>'

)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], '-- Incrementally fetch incidents updated after a given date
SELECT *
FROM get_table_rows
WITH(
TableName=''incident'',

-- Load only incidents updated in the last 24 hours
Query=''sys_updated_on>=<<today-1d,FUN_TO_DATETIME>>''

--OR Updated after static date time
--Query=''sys_updated_on>=2025-01-01T23:23:59''
--Query=''sys_updated_on>=2025-01-01''

--Other examples of placeholder function usage
--Query=''sys_updated_on>=<<monthstart-7d,FUN_TO_DATETIME>>''
--Query=''sys_updated_on>=<<monthstart-5d+1y,FUN_TO_DATETIME>>''

)')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_SERVICENOW_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = '-- Incrementally fetch incidents updated after a given date
SELECT *
FROM get_table_rows
WITH(
TableName=''incident'',

-- Load only incidents updated in the last 24 hours
Query=''sys_updated_on>=<<today-1d,FUN_TO_DATETIME>>''

--OR Updated after static date time
--Query=''sys_updated_on>=2025-01-01T23:23:59''
--Query=''sys_updated_on>=2025-01-01''

--Other examples of placeholder function usage
--Query=''sys_updated_on>=<<monthstart-7d,FUN_TO_DATETIME>>''
--Query=''sys_updated_on>=<<monthstart-5d+1y,FUN_TO_DATETIME>>''

)'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]