Sqlserver 关于update output into从句的实践示例

lusklusklusk

于 2023-07-13 15:48:21 发布

阅读量313

点赞数

分类专栏： sqlserver 文章标签： sqlserver 数据库

本文链接：https://blog.csdn.net/lusklusklusk/article/details/131704207

版权

sqlserver 专栏收录该内容

65 篇文章 1 订阅

订阅专栏

官方文档
https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16

UPDATE后使用OUTPUT INTO从句，OUTPUT是直接输出到结果中，OUTPUT INTO是输出到表中，在OUTPUT子句中,可以引用特殊表inserted和deleted，使用inserted和deleted表与在触发器中使用的非常相似。在INSERT,DELETE,UPDATE中OUTPUT的区别
INSERT：可以引用inserted表以查询新行的属性。
DELETE：可以引用deleted表以查询旧行的属性。
UPDATE：使用deleted表查询被更新行在更改前的属性,用inserted表标识被更新行在更改后的值。

自己编写的例子以便理解

create table test1 (hid int not null,hname nvarchar(100) not null,hdate datetime not null);
insert into test1 values(1,'name1',getdate()-300),(2,'name2',getdate()-200),(3,'name3',getdate()-100),(4,'name4',getdate());

select * from test1--结果
hid	hname	hdate
1	name1	2022-09-16 00:33:59.780
2	name2	2022-12-25 00:33:59.780
3	name3	2023-04-04 00:33:59.780
4	name4	2023-07-13 00:33:59.780

declare @Mytest TABLE(Myid int,Myoldname nvarchar(100),Mynewname nvarchar(100),Myolddate datetime,Mynewdate datetime);

update test1 set hname='updatedname',hdate = getdate()+900   
output inserted.hid,  
      deleted.hname,  
      inserted.hname,  
      deleted.hdate,
      inserted.hdate  
into @Mytest
    WHERE hid <= 3

select * from @Mytest--结果
Myid	Myoldname	Mynewname	Myolddate	Mynewdate
1	name1	updatedname	2022-09-16 00:33:59.780	2025-12-29 00:34:08.937
2	name2	updatedname	2022-12-25 00:33:59.780	2025-12-29 00:34:08.937
3	name3	updatedname	2023-04-04 00:33:59.780	2025-12-29 00:34:08.937

select * from test1--结果
1	updatedname	2025-12-29 00:34:08.937
2	updatedname	2025-12-29 00:34:08.937
3	updatedname	2025-12-29 00:34:08.937
4	name4	    2023-07-13 00:33:59.780

update test1 set hname='newname',hdate = getdate()+900   
output inserted.hid,  
      deleted.hname,  
      inserted.hname,  
      deleted.hdate,
      inserted.hdate
--结果
hid	hname	        hname	hdate	                hdate
1	updatedname	newname	2025-12-29 00:34:08.937	2025-12-29 00:37:12.930
2	updatedname	newname	2025-12-29 00:34:08.937	2025-12-29 00:37:12.930
3	updatedname	newname	2025-12-29 00:34:08.937	2025-12-29 00:37:12.930
4	name4	    newname	2023-07-13 00:33:59.780	2025-12-29 00:37:12.930

select * from test1--结果
hid	hname	hdate
1	newname	2025-12-29 00:37:12.930
2	newname	2025-12-29 00:37:12.930
3	newname	2025-12-29 00:37:12.930
4	newname	2025-12-29 00:37:12.930