时间戳

首页 > 解决方案 > 使用 SQL Server 插入具有自动增量的嵌套实体

sql - 使用 SQL Server 插入具有自动增量的嵌套实体

问题描述

假设我们有具有相同类型属性的类（我将在 C# 中描述类，但这并不重要）

class Exception
{
    public string Message { get; set; }

    public string StackTrace { get; set; }

    public Exception InnerException { get; set; }
}

正如您从上面的代码中看到的，有一个带有嵌套异常的异常类。

让我们创建一个表来存储这些异常

CREATE TABLE Exceptions
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Message] NVARCHAR(MAX) NOT NULL,
    StackTrace NVARCHAR(MAX) NULL,
    InnerExceptionId INT NULL,
    CONSTRAINT FK__Exceptions_Id__Exceptions_InnerExceptionId
        FOREIGN KEY(InnerExceptionId) REFERENCES Exceptions (Id)
);

当然，我可以创建一些可以正常工作的代码：例如，一些以 TVP 作为参数的存储过程，然后循环遍历 TVP 行并一一插入新行。

但是有没有可以通过嵌套优雅地插入异常的 SQL 代码？

标签： sqlsql-server

解决方案

  • 将您的行放在某个列表/集合中，为每一行分配代理 id/rowno，基于该 id/rowno 建立行之间的关系；您每次都可以从 1 开始 - 没关系
  • 将这些数据推送到数据库
  • 将所有行插入持久Exceptions表，获取生成的 IDENTITY 值链接到原始代理 id
  • 将基于代理id的链接转换为基于IDENTITY的链接，更新目标表

所以，@original_list应该是一个输入参数。然后你需要一个MERGE很好的技巧 -插入后src.id链接。dst.id然后只需将旧值转换为新值。所有命令都是基于集合的，没有循环。

DECLARE @original_list TABLE (
  surrogate_no int not null,
  msg varchar(100) not null,
  inner_surrogate_no int null
);

insert into @original_list (surrogate_no, msg, inner_surrogate_no)
values
  (1000, 'err 1000', null),
  (1010, 'err 1010->1000', 1000),
  (1020, 'err 1020', null),
  (1030, 'err 1030->1010', 1010)

-- args prepared, starting migration

DECLARE @migration TABLE (
  src_id int not null,
  dst_id int not null
)

merge Exceptions t
using @original_list s
on 1=0 --<< we are not looking for updates
when not matched by target then
  insert (message) 
  values (s.msg)
output s.surrogate_no, inserted.id ---<<< here is the main trick: src.id and matching dst.id
into @migration(src_id, dst_id)
;

-- now all error messages are inserted, but none of them have InnerExceptionId

update e set
  InnerExceptionId = mp.dst_id
from Exceptions e
inner join @migration m  --<< get original surrogate_no 
  on m.dst_id = e.id
inner join @original_list o --<< grab original row
  on o.surrogate_no = m.src_id
inner join @migration mp  --<< locate dst.id for inner_surrogate_no
  on mp.src_id = o.inner_surrogate_no

这是此类任务的常见解决方案。完整来源

最终数据：

| Id |        Message | StackTrace | InnerExceptionId |
|----|----------------|------------|------------------|
|  1 |       err 1000 |     (null) |           (null) |
|  2 | err 1010->1000 |     (null) |                1 |
|  3 |       err 1020 |     (null) |           (null) |
|  4 | err 1030->1010 |     (null) |                2 |

递归 cte 的树视图：

|                                        Message | Lvl | id | InnerExceptionID |
|------------------------------------------------|-----|----|------------------|
|                                       err 1000 |   1 |  1 |           (null) |
|                                       err 1020 |   1 |  3 |           (null) |
|                    err 1010->1000>>>(err 1000) |   2 |  2 |                1 |
| err 1030->1010>>>(err 1010->1000>>>(err 1000)) |   3 |  4 |                2 |

请注意，sqlfiddle 不允许我在更大的脚本中运行 MERGE（它一直因分号异常而失败），所以我将 @ 表转换为持久表并将合并放入动态 sql，但您不需要这样做真正的服务器。

推荐阅读