TSQL INSERT INTO SELECT-显示行

本文介绍了TSQL INSERT INTO SELECT-显示行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是否可以显示通过此查询插入的行:

Is it possible to display which rows I did inserted via this query:

INSERT INTO dbo.Table (Col1, Col2, Col2)
    (SELECT
        Col1,
        Col2,
       'Something modified',
    FROM dbo.Table
    WHERE Col10 = 66)

重要的是获取不仅具有Col1，Col2，Col3，而且还具有其PrimaryKey值等的全新行

It is important to obtain whole new row with it's PrimaryKey value, etc., not only Col1, Col2, Col3

推荐答案

OUTPUT 子句现在是您的朋友:

INSERT INTO steam.DocumentGeographies (Col1, Col2, Col3)
OUTPUT inserted.*
    (SELECT
        Col1,
        Col2,
       'Something modified',
    FROM dbo.Table
    WHERE Col10 = 66)

您可以通过使用INTO子句指定目标表或将查询包装为子查询来插入OUTPUT的结果:

You can insert the result of the OUTPUT by specifying the destination table with the INTO clause or by wrapping the query as a subquery:

INTO 条款

当您要将相同的数据插入两个表时，这很有用.您始终可以在OUTPUT子句中列出所需的文件(insertted.Col1，insert.Col2)

INSERT INTO steam.DocumentGeographies (Col1, Col2, Col3)
    OUTPUT inserted.* INTO DestinationTableName(Col1, Col2, Col3)
        (SELECT
            Col1,
            Col2,
           'Something modified',
        FROM dbo.Table
        WHERE Col10 = 66)

SUBQUERY

当您要将OUTPUT连接到另一个表或要进行计算(例如对值求和或计数)并将这些结果插入另一个表时，这很有用.

INSERT INTO DestinationTableName
(Col1, Col2, Col3)
SELECT Col1, Col2, Col3 FROM (
    INSERT INTO steam.DocumentGeographies (Col1, Col2, Col3)
    OUTPUT inserted.*
        (SELECT
            Col1,
            Col2,
           'Something modified',
        FROM dbo.Table
        WHERE Col10 = 66)
) TMP

这篇关于TSQL INSERT INTO SELECT-显示行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

col3