存储过程中用INSERT...SELECT触发唯一键冲突，如何实现UPSERT？

The problem with your current query is that the LEFT JOIN doesn’t filter out rows where the id already exists in myTable—it still returns every row from #temp, so trying to insert those duplicate ids triggers the primary key constraint violation. Let’s fix that properly with SQL Server’s built-in upsert capabilities.

Method 1: Use MERGE (Official Upsert Statement) #

SQL Server has a dedicated MERGE statement that handles both updates and inserts in a single query. This is the most concise approach for upserts:

MERGE INTO dbo.myTable AS target
USING #temp AS source
ON target.id = source.id -- Match on the primary key
WHEN MATCHED THEN
    -- Update existing rows with the new payload from #temp
    UPDATE SET payload = source.payload
WHEN NOT MATCHED THEN
    -- Insert new rows where the id doesn't exist in myTable
    INSERT (id, payload)
    VALUES (source.id, source.payload);

How this works: #

• The ON clause checks for matching primary keys between your temporary table (source) and the target table.
• If a match is found (WHEN MATCHED), it updates the payload column in the target table with the value from the source.
• If no match exists (WHEN NOT MATCHED), it inserts the new row from the source into the target.

Method 2: Separate UPDATE + INSERT #

If you prefer more explicit control over each step, split the operation into two separate queries. This is often easier to debug for complex scenarios:

-- Step 1: Update existing rows where the id matches between #temp and myTable
UPDATE target
SET payload = source.payload
FROM dbo.myTable AS target
INNER JOIN #temp AS source ON target.id = source.id;

-- Step 2: Insert only rows from #temp that don't exist in myTable
INSERT INTO dbo.myTable (id, payload)
SELECT source.id, source.payload
FROM #temp AS source
LEFT JOIN dbo.myTable AS target ON source.id = target.id
WHERE target.id IS NULL; -- Filter out rows that already have a match

Critical Precaution: #

Ensure your temporary table #temp doesn’t contain duplicate id values! If #temp has multiple rows with the same id, both methods will throw errors (either a duplicate key violation on insert, or a "multiple rows affecting the same target row" error with MERGE). Add a primary key constraint to #temp to prevent this:

CREATE TABLE #temp (
    id INT NOT NULL PRIMARY KEY,
    payload VARCHAR(100)
);

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