COMMON TABLE EXPRESSION(NEW FEATURE IN 2008 SQL SERVER)

COMMON TABLE EXPRESSION (NEW FEATURE IN 2008 SQL SERVER)

Are used to solve some complex queries that are not possible to solve with simple select statement.

CTE will be treated as temporary table created from the result of a select statement on which you can perform Insert, Update, and Delete.

Syntax:

With <ctename> as

(<select statement>)

<select insert/update/delete statement>

Examples:

Emp table:

Dept Table

Display first 5 rows from the table emp

with empcte as

(select * ,ROW_NUMBER() over(order by empno) as rownum from emp)

select  * from empcte where rownum<=5

Display last 5 rows from table emp

with empcte as

(select * ,ROW_NUMBER() over(order by empno desc) as rownum from emp)

select  * from empcte where rownum<=5

Display 5^th, 7^thand 10^th row from emp

with empcte as

(select * ,ROW_NUMBER() over(order by empno desc) as rownum from emp)

select  * from empcte where rownum in(5,7,10)

Find total and average salary paid to employee deptwise only for the department in which there are more than 2 employees without using havingclause

with empcte as

(select deptno,sum(Sal) as totalsalary, avg(SAl) as averagesal, count(*) as empcount from emp

group by deptno)

select * from empcte where empcount>2

or

with empcte (deptno,totalsalary,averagesalary,empcount)

as

(select deptno,sum(Sal),avg(Sal),count(*) from emp group by (deptno))

select * from empcte where empcount>2

Delete Duplicate rows from table emp

First I am creating a table emp1 and then inserting rows.

select * into emp1 from emp

write this insert syntax three times so that multiple records will be added, as we need to delete duplicate records from the table.

insert emp1 select * from emp

insert emp1 select * from emp

insert emp1 select * from emp

now we will delete duplicate records.

with empcte as

(select *,rank() over(partition by empno,ename,job,mgr,hiredate,deptno order by newid())

as rank from emp1)

delete empcte where rank>1

Explanation:

Create a table emp1 from table emp as follows

Select * into emp1 from emp

2 duplicate the rows in table emp1 by inserting rows from emp table into it as follows

insert emp1 select * from emp

to delete duplicate rows from the table emp1 

write the statement as follows

with empcte as

(select *,rank() over(partition by empno,ename,job,mgr,hiredate,deptno order by newid())

as rank from emp1)

 delete empcte where rank>1