# ERP Incremental Extract Watermark SQL Generator

> Source: https://netray.co/tools/erp-incremental-extract-watermark-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate a T-SQL script that pulls only new and changed ERP rows using a watermark control table.

## Category

developer

## Erp

Microsoft SQL Server

## Audience

- ERP analyst
- ERP developer
- Data engineer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Source schema

**Key:** schema_name

**Type:** text

**Default:** dbo

#### Source table

**Key:** table_name

**Type:** text

**Default:** item_transactions

#### Primary key column

**Key:** key_col

**Type:** text

**Default:** trans_id

#### Last modified column

**Key:** mod_col

**Type:** text

**Default:** RecordDate

**Help:** A datetime column that changes on every insert and update.

#### Columns to extract

**Key:** columns

**Type:** text

**Default:** trans_id, item, whse, qty, trans_type, RecordDate

#### Batch size

**Key:** batch_size

**Type:** number

**Default:** 50000

#### Safety overlap (minutes)

**Key:** overlap_min

**Type:** number

**Default:** 5

**Help:** Re-read this much time before the watermark to catch late-committed transactions.

#### Watermark control table

**Key:** control_table

**Type:** text

**Default:** etl_watermark

**Blocks:**

#### 1. One-time setup: watermark control table

**Body:** IF OBJECT_ID('{{schema_name}}.{{control_table}}') IS NULL
BEGIN
  CREATE TABLE {{schema_name}}.{{control_table}} (
    source_table  sysname      NOT NULL PRIMARY KEY,
    last_mod      datetime2(3) NOT NULL,
    last_run_utc  datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME()
  );
END

IF NOT EXISTS (SELECT 1 FROM {{schema_name}}.{{control_table}} WHERE source_table = '{{table_name}}')
  INSERT INTO {{schema_name}}.{{control_table}} (source_table, last_mod)
  VALUES ('{{table_name}}', '1900-01-01');

#### 2. Each run: read the next batch

**Body:** DECLARE @from datetime2(3);
DECLARE @to   datetime2(3);

SELECT @from = DATEADD(MINUTE, -{{overlap_min}}, last_mod)
FROM {{schema_name}}.{{control_table}}
WHERE source_table = '{{table_name}}';

SELECT TOP ({{batch_size}}) {{columns}}
INTO #batch
FROM {{schema_name}}.{{table_name}}
WHERE {{mod_col}} > @from
ORDER BY {{mod_col}}, {{key_col}};

SELECT @to = MAX({{mod_col}}) FROM #batch;

-- Hand #batch to your loader here, then upsert on {{key_col}} at the destination.
SELECT * FROM #batch;

#### 3. After a successful load: advance the watermark

**Body:** UPDATE {{schema_name}}.{{control_table}}
SET last_mod = @to,
    last_run_utc = SYSUTCDATETIME()
WHERE source_table = '{{table_name}}'
  AND @to IS NOT NULL
  AND @to > last_mod;

DROP TABLE #batch;

#### 4. Reconciliation check

**Body:** SELECT COUNT(*) AS source_rows, MAX({{mod_col}}) AS source_max_mod
FROM {{schema_name}}.{{table_name}}
WHERE {{mod_col}} > '1900-01-01';

-- Compare with the row count and max {{mod_col}} at the destination.

**Notes:** Verify the modified column on your ERP version: many tables use a RecordDate or UpdatedDate that is set by triggers or the application, and some updates bypass it. A rowversion column is more reliable where available. Deletes are not captured by this pattern; use a soft-delete flag or change tracking. The overlap re-reads some rows, so the destination must upsert on the key. A batch ending exactly at a timestamp shared by more than the batch size can stall, so raise the batch size if the watermark stops advancing. Test on a copy and run read-only first.

## Content

**Intro:** Full table dumps get slower every month. An incremental extract pulls only rows that changed since the last run, using a watermark stored in a control table. This generator writes the T-SQL for that pattern with a batch size and a safety overlap.

**Sections:**

#### How to use it

**Body:** Fill in the schema, table, key column and the column that records the last change. List the columns to extract, set a batch size and an overlap in minutes. Copy the four blocks into your job. Run the setup once, then run the read and advance steps on a schedule. Your loader should upsert on the key at the destination so that the overlap does not create duplicates. Use the reconciliation block after each run or daily.

**Bullets:**

- Confirm the modified column really changes on updates
- Upsert at the destination on the primary key
- Keep the overlap larger than your longest write transaction

#### How the watermark works

**Body:** The control table stores the highest modified timestamp that was loaded for each source table. Each run reads rows with a timestamp above the watermark minus a small overlap, ordered by timestamp then key, in a bounded batch. After a successful load the watermark moves forward to the maximum timestamp in the batch. If a run fails before the update, the watermark stays put and the same rows are read again, which makes the process safe to retry.

#### Why the overlap matters

**Body:** A transaction that starts before the watermark but commits after it can write a row with an older timestamp than the rows you already loaded. Without an overlap, that row is missed forever. Re-reading the last few minutes catches it, at the cost of reading some rows twice. Choose an overlap longer than your longest typical write transaction, and rely on the key-based upsert to avoid duplicates.

#### Limits to know

**Body:** This pattern cannot see hard deletes, and it depends on the modified column being reliably maintained. Some ERP tables do not update it for every change. In those cases use rowversion, SQL Server change tracking or change data capture instead, and run a periodic full reconciliation of counts and sums to catch drift.

**Cta:** If you want incremental ERP data feeding a warehouse or an on-prem AI layer, we can design and run the pipeline with you, read-only by default.

**Faq:**

#### Why not just use a full reload each night?

**Answer:** A full reload is simple but grows with the table, and large ERP history tables can take hours. An incremental load touches only changed rows, so runtime and load on the ERP stay stable as data accumulates.

#### What if my table has no modified column?

**Answer:** Look for rowversion, a change tracking setup, or a monotonically increasing key for append-only tables. If none exist, an incremental pull is unreliable, and you may need triggers, change data capture or a periodic full compare.

#### How do I capture deleted rows?

**Answer:** Pull the key list periodically and compare it with the destination, or use change tracking or CDC, which report deletes. Some ERPs soft-delete with a status flag, which this pattern does capture as an update.

#### How big should the overlap be?

**Answer:** Larger than your longest write transaction. Five minutes suits many ERP tables, but a nightly interface that holds a transaction for 30 minutes needs more. The cost of a larger overlap is a few extra rows read, which the upsert handles.

## Search terms

incremental extract sql server, watermark incremental load sql, delta load erp table sql, last modified incremental extract, etl watermark control table, incremental data pull erp to warehouse, rowversion incremental load

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json