field notes
Back to all notes

SQL / Data changes

What happens if I run this SQL script twice?

Conversation: · Kevin Grems

Written up September 22, 2026, from our original discussion.

Adding missing dealer-category associations was only half the job. I also wanted a second run to leave the data alone.

I needed to give existing dealers an association with the Residential building category. Some dealers could already have that association, so inserting one row for every dealer wasn’t enough. The script needed to add only what was missing.

There was also a separate application change for new dealers. Backfilling existing data and making future saves behave correctly were two parts of the same feature.

Define the pair that should exist

The useful unit was the dealer-and-category pair. A dealer might have other categories already, and those selections needed to stay intact. The check had to ask whether this particular association existed.

The pattern was INSERT ... SELECT with a correlated NOT EXISTS. Here’s a standalone illustration using generic account and feature data in temporary tables:

CREATE TABLE #Accounts (AccountID int PRIMARY KEY);
CREATE TABLE #AccountFeatures
(
    AccountID int NOT NULL,
    FeatureID int NOT NULL,
    PRIMARY KEY (AccountID, FeatureID)
);

INSERT INTO #Accounts (AccountID) VALUES (1), (2), (3);
INSERT INTO #AccountFeatures (AccountID, FeatureID) VALUES (1, 100);

DECLARE @Pass int = 1;

WHILE @Pass <= 2
BEGIN
    INSERT INTO #AccountFeatures (AccountID, FeatureID)
    SELECT a.AccountID, 100
    FROM #Accounts AS a
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM #AccountFeatures AS af
        WHERE af.AccountID = a.AccountID
          AND af.FeatureID = 100
    );

    SELECT @Pass AS PassNumber, @@ROWCOUNT AS RowsInserted;
    SET @Pass += 1;
END;

SELECT AccountID, FeatureID FROM #AccountFeatures;

DROP TABLE #AccountFeatures;
DROP TABLE #Accounts;

Two outputs, two different questions

The example inserts two missing associations on its first pass and zero on its second. The final query still returns all three associations.

That distinction came up in our discussion too. I checked that rerunning the backfill returned zero inserted rows, then asked whether the result table listed every dealer with the category. It did. “What changed during this run?” and “What exists now?” are different questions.

The rerun is part of the check

For me, the useful test wasn’t just seeing the expected rows after the first run. It was running the operation again after the first change had been committed and checking that there was nothing left to insert.

A rerunnable script still needs normal review and testing. And NOT EXISTS alone isn’t a concurrency guarantee: a unique constraint on the pair is what enforces that invariant in the database. The sample includes one; that isn’t a claim about the original production table’s constraints.

I like changes where the expected second result is boring. Zero new rows was exactly what I wanted to see.

References: Microsoft on EXISTS / NOT EXISTS and unique constraints.

Back to all notes