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




2018-07-21

blog_main_img

写 SQL Server 的时候，`SELECT` 往往是最容易上手的，真正容易写出事故的反而是 `INSERT`、`UPDATE` 和 `MERGE INTO`

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

本文主要围绕：

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

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

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

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

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

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

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

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

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

BEGIN TRAN;

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

SELECT @@ROWCOUNT AS AffectedRows;

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

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

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

INSERT 不只是 VALUES

最普通的插入大家都熟：

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

一次插多行也很常见：

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

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

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

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

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

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 时顺手去重

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

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

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
);

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

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

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

SQL 防君子，索引防事故。

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

SQL Server 的 UPDATE FROM 很好用。

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

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;

这类写法适合：

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

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

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，要写得更严谨一点：

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

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

用 CASE 做条件更新

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

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

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

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：

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

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

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

大表更新最怕一把梭。

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

可以分批来：

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：

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。

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

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 里硬写。

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

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

确认一下：

SELECT *
FROM #NeedFixUsers;

再更新：

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，也就是：

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

比如同步商品价格：

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 还能顺手删除

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

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

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。

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 会不知道该用哪一行去更新目标行，最后直接报错。

例如：

-- 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 是否重复：

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

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

;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 反而更清楚：

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 那么漂亮，但可读性强，排查也方便。

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

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

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

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 通常比表变量更适合，因为它可以建索引，优化器也更容易做出合理计划。

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

CREATE INDEX IX_PriceList_Sku ON #PriceList(Sku);

用 INSERT EXEC 接住存储过程结果

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

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 列，需要临时开启：

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 个用户打测试标记：

;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：

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 时可以给目标表加锁提示：

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 可以让匹配判断和写入更接近串行化，降低并发插入冲突的概率。

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

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

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

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，可以这样：

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 的最高境界不是把语法写得多花，而是写完之后你敢在生产库按执行。