Enabling Incremental Refresh on a Database Source
Note: The incremental refresh does not take into account deleted documents. A source full refresh or rebuild is required.
In the SQL query, the SELECT statement must have a WHERE clause with a criterion on the last modification date field.
Example: The following simple example should work with common database engines such as Microsoft SQL Server 2012, PostgreSQL, and MySQL. You must replace [PARAMETER] by @LastRefresh in an SqlClient scenario (MSSQL/SQLServer databases) or by ? otherwise (e.g., NorthWind databases). The [PARAMETER] field is sent by the crawler to the query to indicate when the last increment refresh was performed.
<Accessor type="query"
OrderByFieldName="dateCreated"
OrderByFieldType="DateTime"
IncrementalRefreshFieldName="dateModified">
<![CDATA[
Select
id,
title,
dateModified,
content,
author
FROM blog
WHERE dateModified>=[PARAMETER]
order by dateModified
OFFSET @startRow ROWS FETCH NEXT (@endRow-@startRow) ROWS ONLY;
]]>
</Accessor>The example also includes support for pagination (see OFFSET FETCH Clause (SQL Server Compact)).
What's Next?
Ensure that you also created an incremental refresh schedule on your database source in CES (see Scheduling a Source Incremental Refresh).