带OUTPUT的增删改

sql server2005以后引入：

执行的sql语句中加入output可以事实输出处理的内容

go
--插入并返回每行的插入值
DECLARE @NewRows TABLE(Id INT ,NAME NVARCHAR(50),Sorce INT )
INSERT INTO dbo.Student
OUTPUT INSERTED.id,INSERTED.Name,INSERTED.Sorce INTO @NewRows
SELECT Name, Course, Sorce FROM Student WHERE Sorce<90

SELECT * FROM @NewRows


go
--删除返回删除的结果
DECLARE @DelRows TABLE(Id INT,NAME NVARCHAR(50))
DELETE dbo.Student 
OUTPUT DELETED.Id,DELETED.Name INTO @DelRows
WHERE Name='新更改'
SELECT * FROM @DelRows

go
--更新数据返回更新行、新老数据
DECLARE @UpdateRows TABLE(Id INT,OldSorce INT ,NewSource INT)
UPDATE dbo.Student SET Sorce=Sorce+5
OUTPUT INSERTED.Id,DELETED.Sorce,INSERTED.Sorce INTO @UpdateRows
WHERE Name='王五'
SELECT * FROM @UpdateRows