gpt4 book ai didi

sql - INSERT-OUTPUT 包括其他表中的列

转载 作者:行者123 更新时间:2023-12-01 21:59:53 24 4
gpt4 key购买 nike

我有一个存储过程需要插入到三个不同的表中,但我需要获取从一个输入生成的 ID 并使用它插入到下一个表中。我熟悉 INSERT-OUTPUT 构造,但我不确定如何在这种特殊情况下使用它。

DECLARE @guids TABLE ( [GUID] UNIQUEIDENTIFIER );
DECLARE @contacts TABLE ( [ContactID] INT, [GUID] UNIQUEIDENTIFIER );
DECLARE @mappings TABLE ( [TargetID] INT, [GUID] UNIQUEIDENTIFIER );

INSERT @guids ( [GUID] ) ...

INSERT [Contacts] ( [FirstName], [LastName], [ModifiedDate] )
OUTPUT [inserted].[ContactID], g.[GUID]
INTO @contacts
SELECT [First_Name], [Last_Name], GETDATE()
FROM [SourceTable] s
JOIN @guids g ON s.[GUID] = g.[GUID]

INSERT [TargetTable] ( [ContactID], [License], [CreatedDate], [ModifiedDate] )
OUTPUT [inserted].[TargetID], c.[GUID]
INTO @mappings
SELECT c.[ContactID], [License], [CreatedDate], [CreatedDate]
FROM [SourceTable] s
JOIN @contacts c ON s.[GUID] = c.[GUID]

INSERT [Mappings] ( [TargetID], [SourceGUID] )
SELECT [TargetID], [GUID]
FROM @mappings

但我收到以下错误:

The multi-part identifier "g.GUID" could not be bound.

The multi-part identifier "c.GUID" could not be bound.

如果我使用s.GUID,我会得到类似的错误。是否可以在 OUTPUT 子句中进行某种连接?

最佳答案

我不确定这是否是最好的选择,但似乎您可以使用MERGE来实现这一点:

MERGE [Contacts]  trgt
USING
(
SELECT [First_Name], [Last_Name], g.[GUID] as [GUID]
FROM [SourceTable] s
JOIN @guids g ON s.[GUID] = g.[GUID]
)src ON (1=0)
WHEN NOT MATCHED THEN INSERT ( [FirstName], [LastName], [ModifiedDate] )
VALUES (src.[First_Name],src.[Last_Name], GETDATE())
OUTPUT [inserted].[ContactID], src.[GUID]
INTO @contacts

关于sql - INSERT-OUTPUT 包括其他表中的列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15281814/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com