SQL Server 2008中MERGE语句ON 1=1子句的用法及适用场景咨询

Great question! Let’s break down this specific SQL pattern and why someone would use ON 1=1—it’s a clever, narrow-purpose trick that solves a very specific problem.

First, let’s unpack the exact statement you’re looking at:

MERGE T USING S on 1=1 WHEN NOT MATCHED THEN Insert (col1,col2) Values(col1,col2)

Core Logic Breakdown #

Normally, MERGE uses the ON clause to match rows between the target table (T) and source table (S)—think matching primary keys like T.id = S.id to decide whether to update existing rows or insert new ones.

But ON 1=1 is a condition that’s always true. Here’s how that twists the behavior:

• For every row in the source table S, the database checks if there’s any row in T that satisfies 1=1 (which is every row in T, if T isn’t empty).
• If T is empty: There are no rows in T to match against, so every row in S triggers the WHEN NOT MATCHED THEN INSERT clause—all of S gets inserted into T.
• If T has existing rows: Every row in S finds a match (since 1=1 is always true), so the INSERT never runs. No changes are made to T.

When to Use ON 1=1 in This Context #

This pattern is perfect for one-time initialization scenarios where you want to populate a target table only if it’s completely empty. Common use cases include:

• Seeding configuration tables: Suppose you have a table of default system settings. You want to insert these defaults once when the system is first set up, but never re-insert them (even if the source of defaults gets updated later).
• Preventing accidental duplicate bulk inserts: If your source table S is a temporary holding table for initial data, this ensures you don’t accidentally run the insert multiple times and flood T with duplicate rows.
• Idempotent setup scripts: Making sure your deployment/setup script can be run multiple times safely—if the table is already populated, nothing happens; if it’s empty, it gets filled.

How This Differs from Standard MERGE #

In a typical MERGE, you’d use an ON clause like T.col1 = S.col1 to insert only rows from S that don’t already exist in T (based on col1). This ON 1=1 version is much stricter: it only inserts if T has zero rows at all.

Key Caveat #

If this MERGE included a WHEN MATCHED THEN UPDATE clause, it would behave very differently—every row in S would trigger an update on every row in T (since 1=1 matches all pairs), which is almost never what you want. But in your case, without a MATCHED branch, it’s safe and targeted for the empty-table initialization use case.

内容的提问来源于stack exchange，提问作者Raj