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

小贝

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))
3. update #t
4. set name='new value 3'
5. OUTPUT deleted.name,inserted.name into @outputTable
6. where id=2
8. 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)