Friday, September 26, 2014

Take Rows orignal values after update


CREATE TABLE testOutPutClause
(id INT IDENTITY (1,1)
,Name VARCHAR(MAX)
,ModifiedBy INT
,Modifiedon DATETIME
)
INSERT INTO testOutPutClause VALUES('A',1, GETDATE())
INSERT INTO testOutPutClause VALUES('B',1, GETDATE())
INSERT INTO testOutPutClause VALUES('C',1, GETDATE())
INSERT INTO testOutPutClause VALUES('D',1, GETDATE())
INSERT INTO testOutPutClause VALUES('E',1, GETDATE())
DECLARE @tmpTable TABLE(
id INT
,Name VARCHAR(MAX)
,ModifiedOn DATETIME
,ModifiedBy INT
)
UPDATE testOutPutClause
SET Name=Name+'U'
,ModifiedBy = 1 + 10
OUTPUT
deleted.id
,Deleted.Name
,Deleted.Modifiedon
,Deleted.ModifiedBy
 INTO @tmpTable

 SELECT * FROM testOutPutClause

  SELECT * FROM @tmpTable
 
  DROP TABLE testOutPutClause

No comments: