如何在 SQL 中的 OUTPUT 中包含参考列

发布于 2025-01-17 21:14:44 字数 1031 浏览 5 评论 0原文

我有一个参考TableA，其中有一列名为[SomeID]：

SomeID
ABC
DEF
GHI
KLM

我有TableB可以是：

CREATE TABLE TableB([ID] BIGINT, [Name] NVARCHAR(50))

[ID]是主键，并且是自增的。

我想在 TableB 中为 TableA 的每条记录创建一条新记录。

因此，我们这样做：

DECLARE @OuputTable TABLE([ID] BIGINT, [SomeID] NVARCHAR(50))

INSERT INTO TableB([Name])
OUTPUT INSERTED.[ID], 'Need Associated SomeID From TableA Here' INTO @OutputTable
SELECT 'ZZZZZZ' -- Edited this line to remove some possible confusion.
FROM TableA

SELECT *
FROM
@OuputTable

如何在不使用循环的情况下将每个创建的记录的关联 [SomeID] 值放置在 @OuputTable 中？

原文

I have a reference TableA with a single column called [SomeID]:

SomeID
ABC
DEF
GHI
KLM

I have TableB can be:

CREATE TABLE TableB([ID] BIGINT, [Name] NVARCHAR(50))

[ID] is the primary key and is auto-increment.

I want to create a new record in TableB for each record of TableA.

So we do this:

DECLARE @OuputTable TABLE([ID] BIGINT, [SomeID] NVARCHAR(50))

INSERT INTO TableB([Name])
OUTPUT INSERTED.[ID], 'Need Associated SomeID From TableA Here' INTO @OutputTable
SELECT 'ZZZZZZ' -- Edited this line to remove some possible confusion.
FROM TableA

SELECT *
FROM
@OuputTable

How would I be able to place the associated [SomeID] value for each of the created record in @OuputTable without using a loop?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

香草可樂 2025-01-24 21:14:44

您可以尝试使用 MERGE INTO ，这可能允许您在 OUTPUT 中获取源数据值

MERGE INTO TableB AS dest
USING TableA AS sou ON 1=0   
WHEN NOT MATCHED       
    THEN INSERT ([Name])
         VALUES (sou.[SomeID])
OUTPUT INSERTED.[ID], sou.SomeID
INTO @OutputTable (ID, SomeID);

sqlfiddle

You can try to use MERGE INTO which might allow you get source data value in OUTPUT

MERGE INTO TableB AS dest
USING TableA AS sou ON 1=0   
WHEN NOT MATCHED       
    THEN INSERT ([Name])
         VALUES (sou.[SomeID])
OUTPUT INSERTED.[ID], sou.SomeID
INTO @OutputTable (ID, SomeID);

sqlfiddle

回复收藏 0 原文

~没有更多了~