Repeated imports need to add missing records without duplicating yesterday's work. For loading only new rows, define the business key before choosing the query. Then protect that key in both the source preparation and destination table.
Define What Makes a Row New
A new row is a business decision, not simply a different collection of column values. Two product records can share a product code while carrying different descriptions. An insert-only load treats the existing code as already present, even when its description differs.
Decide whether the source represents new entities, changes to existing entities, or both. The examples here insert missing item codes and leave existing quantities unchanged. Updating quantities requires a separate, deliberate rule with its own concurrency protection.
I start import reviews by asking which columns identify the same business entity. I also ask whether that identity remains stable across source corrections. A timestamp helps choose among versions, but it usually does not identify the entity itself.
The destination needs an enforced unique key, not a promise buried in application code. A primary key provides that protection in this example. Reject missing keys rather than quietly treating NULL as a new identity on every run.
Prepare a Source With Duplicate Keys
Run the setup in an isolated test database. The repeated A100 records represent successive source versions. Their distinct source identifiers provide a deterministic tie breaker when two timestamps match.
DROP TABLE IF EXISTS dbo.ItemImportSource;
DROP TABLE IF EXISTS dbo.ItemImportTarget;
CREATE TABLE dbo.ItemImportTarget
(
ItemCode varchar(20) NOT NULL PRIMARY KEY,
Quantity int NOT NULL
);
CREATE TABLE dbo.ItemImportSource
(
SourceId int NOT NULL PRIMARY KEY,
ItemCode varchar(20) NULL,
Quantity int NOT NULL,
ChangedAt datetime2(0) NOT NULL
);
INSERT dbo.ItemImportTarget VALUES ('A100', 4);
INSERT dbo.ItemImportSource VALUES
(1, 'A100', 6, '20250901'),
(2, 'B200', 7, '20250901'),
(3, 'B200', 9, '20250902'),
(4, 'C300', 2, '20250902');Choose the surviving source row before comparing it with the destination. Otherwise, two absent source rows with the same key can both qualify. A unique destination index then rejects the statement, which protects integrity but does not complete the load.
A ROW_NUMBER expression makes the selection rule visible. Order by the newest source timestamp, then by a genuinely unique source identifier. Without a deterministic tie breaker, reruns can select different payloads from tied source versions.
Validate the source independently before insertion. Reject NULL keys, impossible quantities, and malformed values according to the business contract. Do not use deduplication to conceal conflicting records that actually require human review.
Start Loading Only New Rows With NOT EXISTS
The following statement first rejects missing keys and then selects one record per item. NOT EXISTS compares the business key only. Its projection can be a constant because existence, rather than returned values, determines the result.
IF EXISTS
(
SELECT 1 FROM dbo.ItemImportSource WHERE ItemCode IS NULL
)
THROW 51000, 'Source contains a missing item code.', 1;
;WITH Ranked AS
(
SELECT ItemCode, Quantity,
ROW_NUMBER() OVER
(
PARTITION BY ItemCode
ORDER BY ChangedAt DESC, SourceId DESC
) AS VersionRank
FROM dbo.ItemImportSource
)
INSERT dbo.ItemImportTarget (ItemCode, Quantity)
SELECT s.ItemCode, s.Quantity
FROM Ranked AS s
WHERE s.VersionRank = 1
AND NOT EXISTS
(
SELECT 1
FROM dbo.ItemImportTarget AS t
WHERE t.ItemCode = s.ItemCode
);
SELECT @@ROWCOUNT AS AddedRows;
SELECT ItemCode, Quantity
FROM dbo.ItemImportTarget
ORDER BY ItemCode;With these inputs, A100 keeps its original quantity. B200 uses the newest selected source version, and C300 supplies another missing key. My test run reported two added rows, with B200 at quantity 9 from its newest version.
Execute the load statement again without changing either table. On my second run the inserted count was zero, because all selected keys already existed. Capture @@ROWCOUNT immediately after INSERT, before another statement replaces that value.
For loading only new rows, that rerun behavior is the useful starting contract. It covers duplicate submissions of an unchanged source batch. It does not prove that external files, notifications, or unrelated updates are also safe to repeat.
Use EXCEPT Without Comparing the Wrong Columns
EXCEPT returns distinct rows from its left input that are absent from its right input. Apply it to the identity columns when deciding which entities are missing. Comparing quantity alongside the key incorrectly treats a changed quantity as a missing destination row.
The next alternative uses the same source selection rule. Run it after resetting the sample target if you want to compare alternatives independently. Running it after the first load simply exercises the no-new-keys case.
;WITH Ranked AS
(
SELECT ItemCode, Quantity,
ROW_NUMBER() OVER
(
PARTITION BY ItemCode
ORDER BY ChangedAt DESC, SourceId DESC
) AS VersionRank
FROM dbo.ItemImportSource
WHERE ItemCode IS NOT NULL
), Chosen AS
(
SELECT ItemCode, Quantity
FROM Ranked
WHERE VersionRank = 1
), Missing AS
(
SELECT ItemCode FROM Chosen
EXCEPT
SELECT ItemCode FROM dbo.ItemImportTarget
)
INSERT dbo.ItemImportTarget (ItemCode, Quantity)
SELECT c.ItemCode, c.Quantity
FROM Chosen AS c
JOIN Missing AS m ON m.ItemCode = c.ItemCode;
SELECT @@ROWCOUNT AS AddedRows;EXCEPT removes duplicates, but it cannot choose the correct payload for competing source versions. That decision still belongs in Chosen. Both alternatives also require compatible key types and an intentional collation for text identities.
Protect the Gap Between Checking and Inserting
Two concurrent loaders can both find the same key absent. Ordinary NOT EXISTS does not automatically serialize that check. The primary key remains the final guard and causes one conflicting insertion to fail loudly.
When simultaneous loads are expected, protect the destination key ranges inside a transaction. Use an indexed business key so the locking strategy can target relevant ranges. Keep the transaction short and handle deadlocks with a bounded retry of the complete operation.
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
THROW 51001, 'Run this example without an existing transaction.', 1;
BEGIN TRY
BEGIN TRANSACTION;
;WITH Ranked AS
(
SELECT ItemCode, Quantity,
ROW_NUMBER() OVER
(
PARTITION BY ItemCode
ORDER BY ChangedAt DESC, SourceId DESC
) AS VersionRank
FROM dbo.ItemImportSource
WHERE ItemCode IS NOT NULL
)
INSERT dbo.ItemImportTarget (ItemCode, Quantity)
SELECT s.ItemCode, s.Quantity
FROM Ranked AS s
WHERE s.VersionRank = 1
AND NOT EXISTS
(
SELECT 1
FROM dbo.ItemImportTarget AS t WITH (UPDLOCK, SERIALIZABLE)
WHERE t.ItemCode = s.ItemCode
);
DECLARE @AddedRows int = @@ROWCOUNT;
COMMIT TRANSACTION;
SELECT @AddedRows AS AddedRows;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;This protection increases coordination between writers; it does not make contention disappear. Concurrent source changes also deserve a stable source snapshot or an immutable staging batch. A moving source can defeat repeatability even with perfect destination locking.
Make Loading Only New Rows Observable on Every Rerun
Record the batch identity, selected source count, inserted count, and completion status. A zero inserted count can mean a successful rerun or an unexpectedly empty source. Those outcomes need different operational explanations even though INSERT behaves identically.
I test loading only new rows with duplicates, existing keys, and two simultaneous sessions. I also test a forced failure before commit. A rerun should complete from the actual committed state without requiring someone to guess which rows arrived.
Do not enable silent duplicate suppression as a substitute for that review. Hidden rejections make reconciliation harder and leave conflicting payloads unexplained. The unique index is a seat belt, not a sorting hat for bad source data.
Which source correction should replace a previous version, and which should stop the batch? Write that rule beside the ranking expression. Remove these sample tables after testing, and preserve the equivalent validation in the real loading procedure.
Keep rejection counts separate from successful insertion counts. A source batch containing invalid identities should fail validation or produce an explicit rejected-record report. Neither outcome should quietly become a successful empty load.
For large imports, stage and validate the source before opening the destination transaction. Index the staged business key when repeated matching needs it. That keeps sorting and source cleanup outside the protected destination-writing interval.
Related reading on this blog: Finding Duplicate Customers With T-SQL and SQL Server: Find Distinct Result Sets Using EXCEPT Operator.
A repeatable load is not a hopeful existence check, it is an enforced identity contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
