查看: 1149|回复: 0

[SQLServer] SQL Server 使用 OUTPUT做数据操作记录

发表于 2018-4-27 08:00:05

OUTPUT 子句

可以在数据进行增删改的时候,可以返回受影响的行。先准备一张表

  1. create table #t
  2. (
  3. id int identity primary key
  4. ,name varchar(100)
  5. )
  6. go
复制代码

1、insert ,影响行在inserted表里

  1. insert into #t(name)
  2. output inserted.*
  3. values('a')<br><br><br>
复制代码

返回结果:

id name
----------- ----------------------------------------------------------------------------------------------------
1 a

批量插入:

  1. insert into #t
  2. output inserted.*
  3. select 'b'
复制代码

id name
----------- ----------------------------------------------------------------------------------------------------
2 b


2、delete ,影响行在deleted表里

  1. delete from #t
  2. output deleted.id
  3. where id = 1
复制代码

返回结果:

id
-----------
1


3、update,会将新数据放在inserted表里,老数据放在deleted表里

  1. update #t
  2. set name='new value'
  3. OUTPUT deleted.id,deleted.name,inserted.id,inserted.name
  4. where id=2
复制代码

id name id name
----------- ---------------------------------------------------------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
2 d 2 new value

(1 row(s) affected)


4、OUTPUT INTO 支持将数据 插入到表里

  1. DECLARE @outputTable TABLE(name1 varchar(100),name2 varchar(100))
  2. update #t
  3. set name='new value 3'
  4. OUTPUT deleted.name,inserted.name into @outputTable
  5. where id=2
  6. SELECT * FROM @outputTable
复制代码

(1 row(s) affected)
name1 name2
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
new value new value 3

(1 row(s) affected)

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms177564(v%3dsql.90)



回复

使用道具 举报