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')

GET Nth Maximum Salary in SQL Server

In Below Post I have explained 2 way to get nth maximum salary in SQL Server.

Create Temperary Table  

create table #Employee

(

  Id int   Identity(1,1), 

  Name varchar(10) not null,

  salary numeric(18,2) not null,

  Email varchar(50)

)

insert into #Employee (Name,salary,Email)

values('Manish',1000,'ak@gmail.com'),

('Anill',2000,'Anill@gmail.com'),

('Sunil',1200,'Sunil@gmail.com'),

('Ram',1700,'ram@gmail.com'),

('Arun',1800,'arun@gmail.com'),

('Neha',2000,'Neha@gmail.com'),

('Vivek',2100,'Vivek@gmail.com')

---Nth highest salary using correlated subquery

DECLARE @N int=2

SELECT name, salary ,Email

FROM #Employee e1

WHERE @N-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2

WHERE e2.salary > e1.salary)

--Nth highest salary in SQL SERVER using TOP keyword

SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP 2 salary

FROM #Employee

ORDER BY salary DESC

) AS temp

ORDER BY salary