文章摘要
这篇文章介绍了SQL Server 2005中的 OUTPUT 子句，用于在INSERT、DELETE、UPDATE语句中返回修改后的数据，供客户端调用。
- **插入语句**：在INSERT中使用OUTPUT子句可返回新行的属性值，支持INSERTED和DELETED表。
- **删除语句**：使用DELETED表获取被删除行的属性值，并支持返回结果。
- **更新语句**：在UPDATE中，DELETED表获取更新前的属性，INSERTED表获取更新后的值。
文章还对比了MySQL和其他数据库的处理方式，强调了SQL Server中INSERT语句返回值的独特性。此外，文章提到即使在单行插入中，使用OUTPUT子句也非常方便，且在多行插入或多行更新中也能高效工作。
需要注意的是，某些语句（如引用本地分区视图、分布式分区视图或远程表的DML语句、包含EXECUTE的INSERT语句等）不支持OUTPUT子句。文章还提供了几个示例，说明如何通过编程将修改后的数据返回给客户端。
OUTPUT是SQL SERVER2005的新特性，可以从数据修改语句中返回输出,可以看作是”返回结果的DML“。
INSERT、DELETE、UPDATE均支持OUTPUT子句。
在OUTPUT子句中,可以引用特殊表inserted和deleted，使用inserted和deleted表与在触发器中使用的非常相似。
对于INSERT，可以引用inserted表以查询新行的属性。对于DELETE，可以引用deleted表以查询旧行的属性。对于UPDATE，使用deleted表查询被更新行在更改前的属性,用inserted表标识被更新行在更改后的值。 输出给调用方(客户端应用程序)输出给表对于包含自增列的表执行多行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
如果要删除数据的同时，还需要记录日志，或者归档数据，在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
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
以下示例使用指定的 和 ，针对 表中的所有工作顺序更新 列。
子句返回所更新表 () 中的值以及 表中的值。 在 子句中使用 表来指定要更新的行。
由于 表上定义了 触发器，因此需要 关键字。
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
下面的示例捕获从 语句的 子句返回的数据，并将这些数据插入另一个表。
语句每天根据在 表中处理的订单更新 表的 列。 如果产品的库存降至 或更低，它还会删除与这些产品对应的行。
本示例捕获已删除的行并将这些行插入另一个表 中，该表跟踪没有库存的产品。
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 子句：
引用本地分区视图、分布式分区视图或远程表的 DML 语句。包含 EXECUTE 语句的 INSERT 语句。不能将 OUTPUT INTO 子句插入视图或行集函数。参数或变量作为 UPDATE 语句的一部分进行了修改，则 OUTPUT 子句将始终返回语句执行之前的参数或变量的值而不是已修改的值返回单列：
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
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。
您可能感兴趣的文章:MySQL 原理与优化之Update 优化SQL语句中的ON DUPLICATE KEY UPDATE使用mysql?ON?DUPLICATE?KEY?UPDATE重复插入时更新方式浅谈MySql?update会锁定哪些范围的数据关于SQL?Update的四种常见写法