sql的插入、更新、合并插入更新的常用操作和常见问题

查错了最多查出一堆奇怪数据，更新错了就可能是“全表同事一起加班”。所以这篇不打算像语法手册一样从 1、2、3 开始背命令，而是聊一些平时写业务 SQL、修数据、做同步时经常用到的写法。

本文主要围绕：

- `INSERT` 怎么写得更顺手
- `UPDATE` 怎么写得更稳
- `MERGE INTO` 怎么做插入、更新、删除同步
- 一些看起来很骚但确实好用的 SQL Server 写法

## 先说一个保命习惯：更新前先 SELECT

写 `UPDATE` 之前，最好先把目标数据查出来。

比如你准备更新用户状态：

```sql
SELECT *
FROM dbo.Users
WHERE Status = 'Pending'
  AND CreatedAt < '2018-01-01';
```

确认数据没问题后，再改成：

```sql
UPDATE dbo.Users
SET Status = 'Expired'
WHERE Status = 'Pending'
  AND CreatedAt < '2018-01-01';
```

这一步看起来朴素，但它能挡住很多灾难。

如果是生产修数据，我更建议再加一层事务：

```sql
BEGIN TRAN;

UPDATE dbo.Users
SET Status = 'Expired'
WHERE Status = 'Pending'
  AND CreatedAt < '2018-01-01';

SELECT @@ROWCOUNT AS AffectedRows;

-- 确认没问题再提交
-- COMMIT;

-- 不对劲就回滚
-- ROLLBACK;
```

别嫌麻烦。生产库不会因为你手速快就表扬你。

## INSERT 不只是 VALUES

最普通的插入大家都熟：

```sql
INSERT INTO dbo.Users(UserName, Age, Status)
VALUES ('Tom', 20, 'Active');
```

一次插多行也很常见：

```sql
INSERT INTO dbo.Users(UserName, Age, Status)
VALUES
    ('Tom', 20, 'Active'),
    ('Jerry', 22, 'Active'),
    ('Alice', 25, 'Pending');
```

真正常用的是 `INSERT INTO ... SELECT ...`。它适合把一张表的数据加工后插到另一张表里：

```sql
INSERT INTO dbo.UserArchive(UserId, UserName, ArchivedAt)
SELECT Id, UserName, GETDATE()
FROM dbo.Users
WHERE Status = 'Deleted';
```

这种写法在数据归档、批量初始化、临时表转正式表时特别好用。

如果要插入后拿到自增 ID，可以用 `OUTPUT`：

```sql
DECLARE @NewUsers TABLE
(
    Id INT,
    UserName NVARCHAR(100)
);

INSERT INTO dbo.Users(UserName, Age, Status)
OUTPUT inserted.Id, inserted.UserName INTO @NewUsers
VALUES
    ('Tom', 20, 'Active'),
    ('Jerry', 22, 'Active');

SELECT *
FROM @NewUsers;
```

`OUTPUT inserted.xxx` 是 SQL Server 里非常实用的能力，插入后不用再按名称或时间反查一遍，干净很多。

## INSERT 时顺手去重

一个常见需求：只插入不存在的数据。

比如有一张标签表，不希望重复插入同名标签：

```sql
INSERT INTO dbo.Tags(TagName)
SELECT v.TagName
FROM (VALUES
    ('Redis'),
    ('SQL Server'),
    ('Docker')
) AS v(TagName)
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.Tags t
    WHERE t.TagName = v.TagName
);
```

这个写法比在程序里先查再插更直接。

当然，如果业务上绝对不能重复，数据库层面最好还是加唯一索引：

```sql
CREATE UNIQUE INDEX UX_Tags_TagName
ON dbo.Tags(TagName);
```

SQL 防君子，索引防事故。

## UPDATE JOIN：批量更新的常用姿势

SQL Server 的 `UPDATE FROM` 很好用。

比如根据订单表汇总用户消费金额：

```sql
UPDATE u
SET u.TotalAmount = s.TotalAmount
FROM dbo.Users u
JOIN (
    SELECT UserId, SUM(Amount) AS TotalAmount
    FROM dbo.Orders
    WHERE Status = 'Paid'
    GROUP BY UserId
) s ON s.UserId = u.Id;
```

这类写法适合：

- 用明细表更新主表
- 用临时表更新正式表
- 用导入表修正业务表
- 批量同步状态、价格、库存

再比如导入了一张临时价格表：

```sql
UPDATE p
SET p.Price = t.NewPrice,
    p.UpdatedAt = GETDATE()
FROM dbo.Products p
JOIN dbo.TempProductPrice t ON t.Sku = p.Sku
WHERE p.Price <> t.NewPrice;
```

最后这个 `WHERE p.Price <> t.NewPrice` 很重要。没变化就别更新，能减少日志、锁和触发器影响。

如果字段可能是 `NULL`，要写得更严谨一点：

```sql
WHERE ISNULL(p.Price, -1) <> ISNULL(t.NewPrice, -1);
```

如果价格可能真的是 `-1`，那就换一个业务上不可能出现的值，或者拆成更完整的 `NULL` 判断。

## 用 CASE 做条件更新

有时候不同条件要更新成不同值，不一定要写多条 SQL。

```sql
UPDATE dbo.Orders
SET RiskLevel =
    CASE
        WHEN Amount >= 10000 THEN 'High'
        WHEN Amount >= 1000 THEN 'Medium'
        ELSE 'Low'
    END
WHERE Status = 'Paid';
```

还可以只更新某些字段，另一些保持原值：

```sql
UPDATE dbo.Users
SET
    NickName = CASE
        WHEN @NickName IS NULL THEN NickName
        ELSE @NickName
    END,
    Phone = CASE
        WHEN @Phone IS NULL THEN Phone
        ELSE @Phone
    END
WHERE Id = @UserId;
```

这种写法常见于“用户只传了部分字段”的更新接口。

更短一点可以用 `COALESCE`：

```sql
UPDATE dbo.Users
SET
    NickName = COALESCE(@NickName, NickName),
    Phone = COALESCE(@Phone, Phone)
WHERE Id = @UserId;
```

不过要小心：如果业务允许把字段更新成 `NULL`，这种写法就不合适了，因为它会把 `NULL` 当成“不更新”。

## UPDATE TOP：分批处理，别一口吃完

大表更新最怕一把梭。

比如要把几百万条历史数据改状态，直接一条 `UPDATE` 可能造成长事务、锁等待、日志暴涨。

可以分批来：

```sql
WHILE 1 = 1
BEGIN
    UPDATE TOP (1000) dbo.Orders
    SET Status = 'Archived',
        UpdatedAt = GETDATE()
    WHERE Status = 'Finished'
      AND FinishedAt < '2018-01-01';

    IF @@ROWCOUNT = 0
        BREAK;
END;
```

如果要让顺序更可控，可以结合 CTE：

```sql
WHILE 1 = 1
BEGIN
    ;WITH cte AS
    (
        SELECT TOP (1000) Id
        FROM dbo.Orders
        WHERE Status = 'Finished'
          AND FinishedAt < '2018-01-01'
        ORDER BY Id
    )
    UPDATE o
    SET Status = 'Archived',
        UpdatedAt = GETDATE()
    FROM dbo.Orders o
    JOIN cte ON cte.Id = o.Id;

    IF @@ROWCOUNT = 0
        BREAK;
END;
```

这个写法更适合生产环境慢慢跑，别把数据库一脚油门踩到红线。

## UPDATE 配合 OUTPUT：边更新边记录

SQL Server 的 `OUTPUT` 不只可以用在 `INSERT`，也可以用在 `UPDATE`。

比如更新订单状态时，把变更前后的状态记录下来：

```sql
DECLARE @ChangeLog TABLE
(
    OrderId INT,
    OldStatus VARCHAR(20),
    NewStatus VARCHAR(20),
    ChangedAt DATETIME
);

UPDATE dbo.Orders
SET Status = 'Closed',
    UpdatedAt = GETDATE()
OUTPUT
    inserted.Id,
    deleted.Status,
    inserted.Status,
    GETDATE()
INTO @ChangeLog(OrderId, OldStatus, NewStatus, ChangedAt)
WHERE Status = 'Timeout';

SELECT *
FROM @ChangeLog;
```

这里有两个很关键的虚拟表：

- `inserted`：更新后的新数据
- `deleted`：更新前的旧数据

这招适合做审计、修数据留痕、调试批量更新结果。

## 临时表是修数据的好朋友

如果要修一批复杂数据，不要把所有逻辑都塞进一条 SQL 里硬写。

可以先把目标数据放进临时表：

```sql
SELECT Id, UserName, Status
INTO #NeedFixUsers
FROM dbo.Users
WHERE Status = 'Unknown'
  AND CreatedAt < '2018-01-01';
```

确认一下：

```sql
SELECT *
FROM #NeedFixUsers;
```

再更新：

```sql
UPDATE u
SET u.Status = 'Inactive',
    u.UpdatedAt = GETDATE()
FROM dbo.Users u
JOIN #NeedFixUsers f ON f.Id = u.Id;
```

临时表的好处是：你能把“找数据”和“改数据”拆开。脑子清楚，SQL 也清楚。

## MERGE INTO：一条 SQL 搞定有则更新、无则插入

`MERGE INTO` 最常见的用途是 upsert，也就是：

- 目标表有数据，就更新
- 目标表没有数据，就插入

比如同步商品价格：

```sql
MERGE INTO dbo.Products AS target
USING dbo.TempProducts AS source
ON target.Sku = source.Sku
WHEN MATCHED THEN
    UPDATE SET
        target.ProductName = source.ProductName,
        target.Price = source.Price,
        target.UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, ProductName, Price, CreatedAt, UpdatedAt)
    VALUES (source.Sku, source.ProductName, source.Price, GETDATE(), GETDATE());
```

这段 SQL 的意思很直白：

- 用 `Sku` 判断目标表是否已经存在
- 存在就更新商品名和价格
- 不存在就插入新商品

导入 Excel、同步第三方数据、处理中间表时，这个写法很常见。

## MERGE 还能顺手删除

有些同步场景是“目标表要和来源表保持完全一致”。

比如来源表里没有的商品，目标表也要下架：

```sql
MERGE INTO dbo.Products AS target
USING dbo.TempProducts AS source
ON target.Sku = source.Sku
WHEN MATCHED THEN
    UPDATE SET
        target.ProductName = source.ProductName,
        target.Price = source.Price,
        target.UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, ProductName, Price, CreatedAt, UpdatedAt)
    VALUES (source.Sku, source.ProductName, source.Price, GETDATE(), GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET
        target.Status = 'Offline',
        target.UpdatedAt = GETDATE();
```

这里没有直接 `DELETE`，而是把状态改成 `Offline`。

实际业务里我更推荐软删除或状态下架，因为真删数据太干脆，回头查问题会很难受。

## MERGE 配合 OUTPUT：同步了什么，一眼看清

`MERGE` 也支持 `OUTPUT`，而且有一个很好用的 `$action`。

```sql
DECLARE @MergeLog TABLE
(
    ActionName NVARCHAR(10),
    ProductId INT,
    Sku NVARCHAR(50),
    OldPrice DECIMAL(18, 2),
    NewPrice DECIMAL(18, 2)
);

MERGE INTO dbo.Products AS target
USING dbo.TempProducts AS source
ON target.Sku = source.Sku
WHEN MATCHED THEN
    UPDATE SET
        target.Price = source.Price,
        target.UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, ProductName, Price, CreatedAt, UpdatedAt)
    VALUES (source.Sku, source.ProductName, source.Price, GETDATE(), GETDATE())
OUTPUT
    $action,
    inserted.Id,
    inserted.Sku,
    deleted.Price,
    inserted.Price
INTO @MergeLog(ActionName, ProductId, Sku, OldPrice, NewPrice);

SELECT *
FROM @MergeLog;
```

`$action` 会告诉你这一行到底是：

- `INSERT`
- `UPDATE`
- `DELETE`

做数据同步时，这个日志非常舒服。同步完不是只知道“成功了”，而是知道插了多少、改了多少、改了什么。

## MERGE 的一个大坑：source 不能有重复匹配

`MERGE` 看起来优雅，但它不是随便写都安全。

如果 `source` 里同一个 `Sku` 出现多次，而目标表里也有这个 `Sku`，SQL Server 会不知道该用哪一行去更新目标行，最后直接报错。

例如：

```sql
-- TempProducts 里 Sku = 'A001' 出现了两次
MERGE INTO dbo.Products AS target
USING dbo.TempProducts AS source
ON target.Sku = source.Sku
WHEN MATCHED THEN
    UPDATE SET target.Price = source.Price;
```

所以在 `MERGE` 前最好先检查 source 是否重复：

```sql
SELECT Sku, COUNT(*) AS Cnt
FROM dbo.TempProducts
GROUP BY Sku
HAVING COUNT(*) > 1;
```

如果有重复，要先去重。例如取最新一条：

```sql
;WITH source AS
(
    SELECT *
    FROM
    (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY Sku ORDER BY UpdatedAt DESC) AS rn
        FROM dbo.TempProducts
    ) t
    WHERE rn = 1
)
MERGE INTO dbo.Products AS target
USING source
ON target.Sku = source.Sku
WHEN MATCHED THEN
    UPDATE SET
        target.Price = source.Price,
        target.UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, ProductName, Price, CreatedAt, UpdatedAt)
    VALUES (source.Sku, source.ProductName, source.Price, GETDATE(), GETDATE());
```

这个去重 CTE 是 `MERGE` 的老搭档。

## MERGE 不是任何时候都比 UPDATE + INSERT 好

`MERGE` 很酷，但不是所有场景都必须用它。

如果逻辑很简单，有时候拆成两条 SQL 反而更清楚：

```sql
UPDATE target
SET target.Price = source.Price,
    target.UpdatedAt = GETDATE()
FROM dbo.Products target
JOIN dbo.TempProducts source ON source.Sku = target.Sku;

INSERT INTO dbo.Products(Sku, ProductName, Price, CreatedAt, UpdatedAt)
SELECT source.Sku, source.ProductName, source.Price, GETDATE(), GETDATE()
FROM dbo.TempProducts source
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.Products target
    WHERE target.Sku = source.Sku
);
```

这种写法虽然没有 `MERGE` 一条 SQL 那么漂亮，但可读性强，排查也方便。

尤其是复杂业务规则很多时，把更新和插入分开，通常更稳。

## 用表变量或临时表模拟批量参数

后端经常需要批量更新一批数据。与其循环一条一条更新，不如把数据放到表变量或临时表里，一次性处理。

```sql
DECLARE @PriceList TABLE
(
    Sku NVARCHAR(50),
    NewPrice DECIMAL(18, 2)
);

INSERT INTO @PriceList(Sku, NewPrice)
VALUES
    ('A001', 99.00),
    ('A002', 199.00),
    ('A003', 299.00);

UPDATE p
SET p.Price = l.NewPrice,
    p.UpdatedAt = GETDATE()
FROM dbo.Products p
JOIN @PriceList l ON l.Sku = p.Sku;
```

这比程序里 for 循环执行 1000 次 `UPDATE` 优雅多了，数据库也更喜欢这种集合式处理。

如果数据量很大，临时表 `#TempTable` 通常比表变量更适合，因为它可以建索引，优化器也更容易做出合理计划。

```sql
CREATE TABLE #PriceList
(
    Sku NVARCHAR(50) NOT NULL,
    NewPrice DECIMAL(18, 2) NOT NULL
);

CREATE INDEX IX_PriceList_Sku ON #PriceList(Sku);
```

## 用 INSERT EXEC 接住存储过程结果

有些老系统喜欢把查询逻辑写在存储过程里。如果你想把存储过程结果插入临时表，可以这样：

```sql
CREATE TABLE #UserResult
(
    UserId INT,
    UserName NVARCHAR(100),
    Status VARCHAR(20)
);

INSERT INTO #UserResult(UserId, UserName, Status)
EXEC dbo.GetActiveUsers;

SELECT *
FROM #UserResult;
```

接下来就可以基于 `#UserResult` 做更新、插入或对账。

这个写法在维护历史系统时挺常见。

## 身份列插入：IDENTITY_INSERT

有时迁移数据，需要保留原来的自增 ID。

SQL Server 默认不允许显式插入 identity 列，需要临时开启：

```sql
SET IDENTITY_INSERT dbo.Users ON;

INSERT INTO dbo.Users(Id, UserName, Age)
VALUES (1001, 'Tom', 20);

SET IDENTITY_INSERT dbo.Users OFF;
```

注意：同一时间一个会话里只能对一张表开启 `IDENTITY_INSERT`。

迁移脚本里用完一定要关掉，不然下一段脚本可能会一脸茫然。

## 更新随机数据：NEWID 的妙用

想随机抽一批数据更新，可以用 `NEWID()`。

比如随机挑 100 个用户打测试标记：

```sql
;WITH cte AS
(
    SELECT TOP (100) Id
    FROM dbo.Users
    WHERE Status = 'Active'
    ORDER BY NEWID()
)
UPDATE u
SET u.Tag = 'TestGroupA'
FROM dbo.Users u
JOIN cte ON cte.Id = u.Id;
```

这个写法适合测试、抽样、灰度分组。

不过大表上 `ORDER BY NEWID()` 会比较重，不适合随便在超大表上跑。

## 插入不存在，存在就加数量

库存、计数类场景里经常有这种逻辑：有记录就累加，没有记录就插入。

可以用 `MERGE`：

```sql
MERGE INTO dbo.Inventory AS target
USING (SELECT 'A001' AS Sku, 10 AS Quantity) AS source
ON target.Sku = source.Sku
WHEN MATCHED THEN
    UPDATE SET
        target.Quantity = target.Quantity + source.Quantity,
        target.UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, Quantity, CreatedAt, UpdatedAt)
    VALUES (source.Sku, source.Quantity, GETDATE(), GETDATE());
```

这类写法很适合库存入库、积分累加、统计汇总。

如果并发很高，还要考虑唯一索引、事务隔离级别和锁，不要只靠 SQL 看起来顺眼。

## 用 WITH HOLDLOCK 降低并发 upsert 风险

并发 upsert 有个经典问题：两个请求同时判断“不存在”，然后都去插入，最后撞唯一索引。

使用 `MERGE` 时可以给目标表加锁提示：

```sql
MERGE INTO dbo.Inventory WITH (HOLDLOCK) AS target
USING (SELECT 'A001' AS Sku, 10 AS Quantity) AS source
ON target.Sku = source.Sku
WHEN MATCHED THEN
    UPDATE SET
        target.Quantity = target.Quantity + source.Quantity
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, Quantity)
    VALUES (source.Sku, source.Quantity);
```

`HOLDLOCK` 可以让匹配判断和写入更接近串行化，降低并发插入冲突的概率。

但锁不是免费午餐。并发高、表大、事务长时，锁也可能带来等待。关键业务还是要结合唯一索引和重试机制一起设计。

## 做修复脚本时，最好把影响行数打出来

修数据时我喜欢每一步都输出影响行数：

```sql
BEGIN TRAN;

UPDATE dbo.Users
SET Status = 'Inactive'
WHERE LastLoginAt < '2018-01-01'
  AND Status = 'Active';

PRINT CONCAT('Updated Users: ', @@ROWCOUNT);

-- COMMIT;
-- ROLLBACK;
```

如果 SQL Server 版本较老不支持 `CONCAT`，可以这样：

```sql
PRINT 'Updated Users: ' + CAST(@@ROWCOUNT AS VARCHAR(20));
```

别小看这个输出。脚本跑完你能立刻知道影响范围对不对，不用再猜。

## 最后聊聊这些写法该怎么选

如果只是单表更新，普通 `UPDATE` 就够了。

如果需要根据另一张表更新，用 `UPDATE FROM JOIN`。

如果要批量插入查询结果，用 `INSERT INTO SELECT`。

如果要“有则更新，无则插入”，可以用 `MERGE INTO`，也可以拆成 `UPDATE + INSERT`。

如果要同步并记录变化，用 `OUTPUT`。

如果数据量大，分批处理，别一口气更新全表。

如果是生产修数据，上事务、先 SELECT、看 `@@ROWCOUNT`，别把希望寄托在撤回键上。

SQL Server 的这些写法本身不复杂，真正的难点是边界意识：会不会重复、会不会并发、会不会锁表、会不会误更新、能不能回滚、有没有日志。

写 SQL 的最高境界不是把语法写得多花，而是写完之后你敢在生产库按执行。