sql - 如何使用插入行的标识值更新第二个表到第一个表中

Question

我有以下表格结构

CREATE TABLE [dbo].[WorkItem](
    [WorkItemId] [int] IDENTITY(1,1) NOT NULL,
    [WorkItemTypeId] [int] NOT NULL,
    [ActionDate] [datetime] NOT NULL,
    [WorkItemStatusId] [int] NOT NULL,
    [ClosedDate] [datetime] NULL,
)

CREATE TABLE [dbo].[RequirementWorkItem](
    [WorkItemId] [int] NOT NULL,
    [RequirementId] [int] NOT NULL,
)

CREATE TABLE #RequirmentWorkItems
(
    RequirementId int,
    WorkItemTypeId int,
    WorkItemStatusId int,
    ActionDate datetime
)

我使用 #RequirmentWorkItems表来为需求创建工作项。然后我需要将工作项插入 WorkItem表并使用来自 WorkItem 的标识值在 RequirementWorkItem 中创建交叉引用行的表 table 。

有没有办法在不通过每一行光标的情况下做到这一点？我不能把 RequirementId进 WorkItem表因为取决于 WorkItemTypeId WorkItem可以链接到 Requirement或 Notice或 Event .

所以 WorkItems 真的有 3 个外部参照表.或者最好放一个 RequirementId , NoticeId和 EventId在 WorkItem表和列中的 1 将有一个值，其他 2 将是空？希望这一切都是有道理的。谢谢。

Answer 1

您应该阅读 MERGE and OUTPUT – the swiss army knife of T-SQL有关这方面的更多信息。

Today I stumbled upon a different use for it, returning values using an OUTPUT clause from a table used as the source of data for an insertion. In other words, if I’m inserting from [tableA] into [tableB] then I may want some values from [tableA] after the fact, particularly if [tableB] has an identity. Observe my first attempt using a straight insertion where I am trying to get a field from @source.[id] that is not used in the insertion: