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

Question

假设我们有具有相同类型属性的类(我将在 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 代码可以通过嵌套优雅地插入异常？

Answer 1

• 将行放入某个列表/集合中，为每行分配代理 id/rowno，根据该 id/rowno 建立行之间的关系；每次都可以从 1 开始 - 没关系
• 将这些数据推送到数据库
• 将所有行插入持久Exceptions表中，获取链接到原始代理ID的生成IDENTITY值
• 将基于代理 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

这是此类任务的常见解决方案。 Full source

最终数据:

| 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 的 TreeView :

|                                        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中，但你不需要在真实服务器上执行此操作。