参考前一个实例使用OUTPUT保存更新记录前后数据,改用一个表变量来实现。
首先定义一个表变量:
DECLARE
@SalaryReport
TABLE
(
[ MemberId ] [ int ] ,
[ Name ] [ nvarchar ] ( 100 ),
[ OldSalary ] [ decimal ] ( 18 , 6 ),
[ NewSalary ] [ decimal ] ( 18 , 6 )
)
(
[ MemberId ] [ int ] ,
[ Name ] [ nvarchar ] ( 100 ),
[ OldSalary ] [ decimal ] ( 18 , 6 ),
[ NewSalary ] [ decimal ] ( 18 , 6 )
)
然后把更新OUTPUT结果INTO到这个表变量中:
UPDATE
[
dbo
]
.
[
Member
]
SET
[
Salary
]
=
[
Salary
]
*
(
1
+
0.05
)
OUTPUT deleted. [ MemberId ] ,deleted. [ Name ] ,deleted. [ Salary ] ,inserted. [ Salary ]
INTO @SalaryReport
SELECT * FROM @SalaryReport
OUTPUT deleted. [ MemberId ] ,deleted. [ Name ] ,deleted. [ Salary ] ,inserted. [ Salary ]
INTO @SalaryReport
SELECT * FROM @SalaryReport
结果: