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

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