数据库教程:SQL Server中带有OUTPUT子句的INSERT,DELETE,UPDATE应用

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

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

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;

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  }