HOW TO INSERT OLD DATA INTO LOG TABLE BEFORE UPDATE/DELETE USING OUTPUT CLAUSE

Implementing the OUTPUT Clause in SQL Server 2008

 CREATE TEMPERAROY TABLE

create table #tempaduit

(

  id int ,

  Name varchar(50),

  Mobile varchar(50),

  Email varchar(50)

)

create table #temp

(

  id int identity (1,1),

  Name varchar(50),

  Mobile varchar(50),

  Email varchar(50)

)

INSERT INTO #temp (Name,Mobile,Email)

values

('Sunil','9899767132','sk@gmail.com'),

('Anil','78787998989','anil@gmail.com')

-- STORE OLD DATA INTO LOG TABLE BEFORE UPDATE

update #temp

   SET Name='Shyam',Email='s@gmail.com',Mobile='4565445564'

   OUTPUT

   deleted.id,deleted.Name,deleted.Mobile,deleted.Email

   INTO  #tempaduit(id,Name,Mobile,Email)

   WHERE id=1

  

-- STORE OLD DATA INTO LOG TABLE BEFORE DELETE

   DELETE FROM #temp (Name,Mobile,Email)

   OUTPUT

   deleted.id,deleted.Name,deleted.Mobile,deleted.Email

   INTO  #tempaduit(id,Name,Mobile,Email)

   WHERE id=2

  

  

-- GET NEWLY INSERTED DATA AS OUTPUT

   INSERT INTO   #temp

   OUTPUT

   inserted.id,inserted.Name,inserted.Mobile,inserted.Email

   INTO  #tempaduit(id,Name,Mobile,Email)

   values('Anil','9899767132','anil@gmail.com')