OUTPUT 子句
可以在数据进行增删改的时候,可以返回受影响的行。先准备一张表
- create table #t
- (
- id int identity primary key
- ,name varchar(100)
- )
- go
复制代码
1、insert ,影响行在inserted表里
- insert into #t(name)
- output inserted.*
- values('a')<br><br><br>
复制代码
返回结果:
id name ----------- ---------------------------------------------------------------------------------------------------- 1 a
批量插入:
- insert into #t
- output inserted.*
- select 'b'
复制代码
id name ----------- ---------------------------------------------------------------------------------------------------- 2 b
2、delete ,影响行在deleted表里
- delete from #t
- output deleted.id
- where id = 1
复制代码
返回结果:
id ----------- 1
3、update,会将新数据放在inserted表里,老数据放在deleted表里
- update #t
- set name='new value'
- OUTPUT deleted.id,deleted.name,inserted.id,inserted.name
- where id=2
复制代码
id name id name ----------- ---------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 2 d 2 new value
(1 row(s) affected)
4、OUTPUT INTO 支持将数据 插入到表里
- DECLARE @outputTable TABLE(name1 varchar(100),name2 varchar(100))
- update #t
- set name='new value 3'
- OUTPUT deleted.name,inserted.name into @outputTable
- where id=2
-
- 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)
|