SQL?Server中帶有OUTPUT子句的INSERT,DELETE,UPDATE應(yīng)用

更新時(shí)間：2022年06月01日 15:54:08 作者：springsnow

這篇文章介紹了SQL?Server中帶有OUTPUT子句的INSERT,DELETE,UPDATE應(yīng)用，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

OUTPUT是SQL SERVER2005的新特性，可以從數(shù)據(jù)修改語句中返回輸出,可以看作是"返回結(jié)果的DML"。

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

在OUTPUT子句中,可以引用特殊表inserted和deleted，使用inserted和deleted表與在觸發(fā)器中使用的非常相似。

在INSERT,DELETE,UPDATE中OUTPUT的區(qū)別

對(duì)于INSERT，可以引用inserted表以查詢新行的屬性。
對(duì)于DELETE，可以引用deleted表以查詢舊行的屬性。
對(duì)于UPDATE，使用deleted表查詢被更新行在更改前的屬性,用inserted表標(biāo)識(shí)被更新行在更改后的值。

輸出方式:

輸出給調(diào)用方(客戶端應(yīng)用程序)
輸出給表

一、應(yīng)用:

1、帶有OUTPUT的INSERT的應(yīng)用

對(duì)于包含自增列的表執(zhí)行多行insert語句,同時(shí)想知道新的標(biāo)識(shí)值時(shí),在INSERT中使用OUTPUT子句非常方便。

1、對(duì)于單行INSERT語句，這不成問題:SCOPE_IDENTITY函數(shù)即可實(shí)現(xiàn)。SCOPE_IDENTITY函數(shù)：返回為當(dāng)前會(huì)話和當(dāng)前作用域中的任何表最后生成的標(biāo)識(shí)值。

-- 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

注意代碼中被注釋掉的第二個(gè)OUTPUT子句，后面沒有INTO子句。如果還要輸出返回給調(diào)用方，取消注釋即可。這樣INSERT語句將包含兩個(gè)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的應(yīng)用.

如果要?jiǎng)h除數(shù)據(jù)的同時(shí)，還需要記錄日志，或者歸檔數(shù)據(jù)，在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的應(yīng)用

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，針對(duì) WorkOrder 表中的所有工作順序更新 ScrapReasonID 列。

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

由于 WorkOrder 表上定義了 AFTER UPDATE 觸發(fā)器，因此需要 INTO 關(guān)鍵字。

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 子句返回的數(shù)據(jù)，并將這些數(shù)據(jù)插入另一個(gè)表。

MERGE 語句每天根據(jù)在 Quantity 表中處理的訂單更新 ProductInventory 表的 SalesOrderDetail 列。如果產(chǎn)品的庫存降至 0 或更低，它還會(huì)刪除與這些產(chǎn)品對(duì)應(yīng)的行。

本示例捕獲已刪除的行并將這些行插入另一個(gè)表 ZeroInventory 中，該表跟蹤沒有庫存的產(chǎn)品。

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子句的注意事項(xiàng)：

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

引用本地分區(qū)視圖、分布式分區(qū)視圖或遠(yuǎn)程表的 DML 語句。
包含 EXECUTE 語句的 INSERT 語句。
不能將 OUTPUT INTO 子句插入視圖或行集函數(shù)。
參數(shù)或變量作為 UPDATE 語句的一部分進(jìn)行了修改，則 OUTPUT 子句將始終返回語句執(zhí)行之前的參數(shù)或變量的值而不是已修改的值

三、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);";

    // 通過數(shù)據(jù)庫
    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

以上就是本文的全部內(nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQL?Server中帶有OUTPUT子句的INSERT,DELETE,UPDATE應(yīng)用

在INSERT,DELETE,UPDATE中OUTPUT的區(qū)別

輸出方式:

一、應(yīng)用:

1、帶有OUTPUT的INSERT的應(yīng)用

2、帶有OUTPUT的DELETE的應(yīng)用.

3、帶有OUTPUT的UPDATE的應(yīng)用

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

4、MERGE語句

二、使用OUTPUT子句的注意事項(xiàng)：

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

四、參考：

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQL?Server中帶有OUTPUT子句的INSERT,DELETE,UPDATE應(yīng)用

在INSERT,DELETE,UPDATE中OUTPUT的區(qū)別

輸出方式:

一、應(yīng)用:

1、帶有OUTPUT的INSERT的應(yīng)用

2、帶有OUTPUT的DELETE的應(yīng)用.

3、帶有OUTPUT的UPDATE的應(yīng)用

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

4、MERGE語句

二、使用OUTPUT子句的注意事項(xiàng)：

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

四、參考：

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

一、應(yīng)用:

3、帶有OUTPUT的UPDATE的應(yīng)用

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

4、MERGE語句

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

四、參考：