SQL Server中带有OUTPUT子句的INSERT,DELETE,UPDATE应用

OUTPUT是SQL SERVER2005的新特性，可以从数据修改语句中返回输出,可以看作是"返回结果的DML"。

INSERT、DELETE、UPDATE均支持OUTPUT子句。

在OUTPUT子句中,可以引用特殊表inserted和deleted，使用inserted和deleted表与在触发器中使用的非常相似。

在INSERT,DELETE,UPDATE中OUTPUT的区别

• 对于INSERT，可以引用inserted表以查询新行的属性。
• 对于DELETE，可以引用deleted表以查询旧行的属性。
• 对于UPDATE，使用deleted表查询被更新行在更改前的属性,用inserted表标识被更新行在更改后的值。

输出方式:

• 输出给调用方(客户端应用程序)
• 输出给表

一、应用:

1、带有OUTPUT的INSERT的应用

对于包含自增列的表执行多行insert语句,同时想知道新的标识值时,在INSERT中使用OUTPUT子句非常方便。

1、对于单行INSERT语句，这不成问题:SCOPE_IDENTITY函数即可实现。SCOPE_IDENTITY函数：返回为当前会话和当前作用域中的任何表最后生成的标识值。

-- Generating Surrogate Keys for Customers
USE tempdb;
GO
IF OBJECT_ID('dbo.CustomersDim') IS NOT NULL
  DROP TABLE dbo.CustomersDim;
GO  

CREATE TABLE dbo.CustomersDim
(
  KeyCol      INT          NOT NULL IDENTITY PRIMARY KEY,
  CustomerID  NCHAR(5)     NOT NULL,
  CompanyName NVARCHAR(40) NOT NULL,  

);  

-- Insert New Customers and Get their Surrogate Keys
DECLARE @NewCusts TABLE
(
  CustomerID NCHAR(5) NOT NULL PRIMARY KEY,
  KeyCol     INT      NOT NULL UNIQUE
);  

INSERT INTO dbo.CustomersDim(CustomerID, CompanyName)
       OUTPUT inserted.CustomerID, inserted.KeyCol    INTO @NewCusts
    -- OUTPUT inserted.CustomerID, inserted.KeyCol
  SELECT CustomerID, CompanyName  FROM Northwind.dbo.Customers    WHERE Country = N'UK';  

SELECT CustomerID, KeyCol FROM @NewCusts;
GO

注意代码中被注释掉的第二个OUTPUT子句，后面没有INTO子句。如果还要输出返回给调用方，取消注释即可。这样INSERT语句将包含两个OUTPUT子句。

2、多行INSERT语句

USE AdventureWorks;
GO 

CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))  

DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))  

INSERT TestTable (ID, TEXTVal)
    OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
     OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')  

SELECT * FROM @TmpTable
SELECT * FROM TestTable  

DROP TABLE TestTable
GO

2、带有OUTPUT的DELETE的应用.

如果要删除数据的同时，还需要记录日志，或者归档数据，在DELETE中使用OUTPUT子句在适合不过了。

USE AdventureWorks;
GO  

CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))  DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))  

INSERT TestTable (ID, TEXTVal)  VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)  VALUES (2,'SecondVal')  

DELETE  FROM TestTable
   OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)  

SELECT * FROM @TmpTable
SELECT * FROM TestTable  

DROP TABLE TestTable
GO

3、带有OUTPUT的UPDATE的应用

USE AdventureWorks;
GO  

CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))  

DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))  

INSERT TestTable (ID, TEXTVal)  VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)  VALUES (2,'SecondVal')  

UPDATE TestTable   SET TEXTVal = 'NewValue'
  OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)  

SELECT * FROM @TmpTable
SELECT * FROM TestTable  

DROP TABLE TestTable
GO

4、在 UPDATE 语句中使用包含 from_table_name 的 OUTPUT INTO

以下示例使用指定的 ProductID 和 ScrapReasonID，针对 WorkOrder 表中的所有工作顺序更新 ScrapReasonID 列。

OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。 在 Product 子句中使用 FROM 表来指定要更新的行。

由于 WorkOrder 表上定义了 AFTER UPDATE 触发器，因此需要 INTO 关键字。

USE AdventureWorks2012;
GO

DECLARE @MyTestVar TABLE (
    OldScrapReasonID INT NOT NULL,
    NewScrapReasonID INT NOT NULL,
    WorkOrderID INT NOT NULL,
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50)NOT NULL);  

UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT deleted.ScrapReasonID,
       inserted.ScrapReasonID,
       inserted.WorkOrderID,
       inserted.ProductID,
       p.Name
    INTO @MyTestVar
FROM Production.WorkOrder AS wo
    INNER JOIN Production.Product AS p
    ON wo.ProductID = p.ProductID
    AND wo.ScrapReasonID= 16
    AND p.ProductID = 733;  

SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
    ProductID, ProductName
FROM @MyTestVar;
GO

4、MERGE语句

下面的示例捕获从 OUTPUT 语句的 MERGE 子句返回的数据，并将这些数据插入另一个表。

MERGE 语句每天根据在 Quantity 表中处理的订单更新 ProductInventory 表的 SalesOrderDetail 列。 如果产品的库存降至 0 或更低，它还会删除与这些产品对应的行。

本示例捕获已删除的行并将这些行插入另一个表 ZeroInventory 中，该表跟踪没有库存的产品。

USE AdventureWorks2012;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);
GO  

INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
FROM
(   MERGE Production.ProductInventory AS pi
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
           JOIN Sales.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
           AND soh.OrderDate = '20070401'
           GROUP BY ProductID) AS src (ProductID, OrderQty)
    ON (pi.ProductID = src.ProductID)
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $action, deleted.ProductID)
AS Changes (Action, ProductID)
WHERE Action = 'DELETE';  

IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO  

SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;

二、使用OUTPUT子句的注意事项：

以下语句中不支持 OUTPUT 子句：

• 引用本地分区视图、分布式分区视图或远程表的 DML 语句。
• 包含 EXECUTE 语句的 INSERT 语句。
• 不能将 OUTPUT INTO 子句插入视图或行集函数。
• 参数或变量作为 UPDATE 语句的一部分进行了修改，则 OUTPUT 子句将始终返回语句执行之前的参数或变量的值而不是已修改的值

三、C#中使用cmd.ExecuteScalar（单列）、cmdExecuteReader（多行或多列）

返回单列：

using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(@na,@occ)",con))
    {
        cmd.Parameters.AddWithValue("@na", Mem_NA);
        cmd.Parameters.AddWithValue("@occ", Mem_Occ);
        con.Open();
        int modified =(int)cmd.ExecuteScalar();
        if (con.State == System.Data.ConnectionState.Open)
            con.Close();
       return modified;
    }

返回多行或者多列：

create table Suspension (pkey int not null identity(1, 1),
pallet_position int,
processing_pallet_pkey int,
datetime_created datetime,
datetime_updated datetime,
[this.created_by] int,
[this.updated_by] int);
using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    const string insertQuery = @"
INSERT INTO dbo.Suspension
(pallet_position, processing_pallet_pkey, datetime_created, datetime_updated,
[this.created_by], [this.updated_by])
OUTPUT INSERTED.pkey VALUES
(1, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(2, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(3, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(4, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2);";

    // 通过数据库
    DataTable dt = new DataTable();
    using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
    using (var insertedOutput = cmd.ExecuteReader())
    {
        dt.Load(insertedOutput);
    }
    Console.WriteLine(dt.Rows.Count); // 4

    // 通过手工读取
    var list = new List<int>();
    using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
    using (var insertedOutput = cmd.ExecuteReader())
    {
        while(insertedOutput.Read())
        {
            list.Add(insertedOutput.GetInt32(0));
        }
    }
    Console.WriteLine(list.Count); // 4

    // 通过dapper
    var ids = conn.Query<int>(insertQuery).ToList();
    Console.WriteLine(ids.Count); // 4
}

四、参考：

https://docs.microsoft.com/zh-cn/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-2014

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。