SQL Server中如何在MERGE语句里递增非标识类型主键

Great question! I’ve dealt with this exact scenario a few times when working with older tables that didn’t have identity columns set for their primary keys. Let’s break down the best ways to handle auto-incrementing the PK during a MERGE’s INSERT operation:

1. Use a SEQUENCE Object (Recommended for SQL Server 2012+) #

This is the cleanest and most scalable approach, especially for concurrent operations. Sequences are designed to generate unique, incrementing values without relying on table identity properties.

First, create a sequence that matches your primary key’s data type (adjust the start value, increment, and bounds to fit your table’s needs):

CREATE SEQUENCE dbo.YourTable_PK_Sequence
    AS INT -- Match your PK's data type (e.g., BIGINT for larger datasets)
    START WITH 100 -- Set this to 1 + your current max PK value to avoid conflicts
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 99999999
    CACHE 50; -- Caching improves performance for frequent inserts

Then, integrate the sequence directly into your MERGE statement’s INSERT clause using NEXT VALUE FOR:

MERGE INTO dbo.TargetTable AS Target
USING dbo.SourceData AS Source
    ON Target.PrimaryKeyColumn = Source.MatchingIdentifier
WHEN MATCHED THEN
    UPDATE SET
        Target.ColumnA = Source.ColumnA,
        Target.ColumnB = Source.ColumnB
WHEN NOT MATCHED THEN
    INSERT (PrimaryKeyColumn, ColumnA, ColumnB)
    VALUES (NEXT VALUE FOR dbo.YourTable_PK_Sequence, Source.ColumnA, Source.ColumnB);

2. MAX(PK) + 1 (Legacy Systems Without Sequence Support) #

If you’re working with a SQL Server version older than 2012 (or another database that doesn’t support sequences), you can calculate the next PK value using MAX(). Important: You must use table hints to prevent duplicate PKs in concurrent environments.

For single-row or small batch inserts:

MERGE INTO dbo.TargetTable AS Target
USING (
    SELECT 
        Source.*,
        -- Lock the table temporarily to avoid race conditions
        (SELECT ISNULL(MAX(PrimaryKeyColumn), 0) + 1 FROM dbo.TargetTable WITH (UPDLOCK, HOLDLOCK)) AS NewPK
    FROM dbo.SourceData AS Source
) AS Source
    ON Target.PrimaryKeyColumn = Source.MatchingIdentifier
WHEN MATCHED THEN
    UPDATE SET
        Target.ColumnA = Source.ColumnA,
        Target.ColumnB = Source.ColumnB
WHEN NOT MATCHED THEN
    INSERT (PrimaryKeyColumn, ColumnA, ColumnB)
    VALUES (Source.NewPK, Source.ColumnA, Source.ColumnB);

For multi-row inserts, combine MAX() with ROW_NUMBER() to generate sequential PKs:

MERGE INTO dbo.TargetTable AS Target
USING (
    SELECT 
        Source.*,
        -- Base value + row number to create sequential PKs for all new rows
        (SELECT ISNULL(MAX(PrimaryKeyColumn), 0) FROM dbo.TargetTable WITH (UPDLOCK, HOLDLOCK)) 
        + ROW_NUMBER() OVER (ORDER BY Source.SomeSortColumn) AS NewPK
    FROM dbo.SourceData AS Source
) AS Source
    ON Target.PrimaryKeyColumn = Source.MatchingIdentifier
WHEN MATCHED THEN
    UPDATE SET
        Target.ColumnA = Source.ColumnA,
        Target.ColumnB = Source.ColumnB
WHEN NOT MATCHED THEN
    INSERT (PrimaryKeyColumn, ColumnA, ColumnB)
    VALUES (Source.NewPK, Source.ColumnA, Source.ColumnB);

Key Considerations #

• Avoid Conflicts: Before using a sequence, run SELECT MAX(PrimaryKeyColumn) FROM dbo.TargetTable to set the sequence’s START WITH value to 1 higher than the current maximum.
• Concurrency Risks: The MAX() + 1 method relies on table locks (UPDLOCK, HOLDLOCK) to prevent race conditions. This can impact performance in high-traffic environments, so sequences are always preferred if available.
• Data Type Matching: Ensure your sequence or calculated PK value matches the data type of your primary key column (e.g., don’t use an INT sequence for a BIGINT PK).

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