Use of common table expression (CTE) in SSMS

A common table expression (CTE) can be thought of as a temporary result set.

A CTE is similar to a derived table in that it is not stored as an object.
We can use once after Define it.We can create recursive query by CTE.
single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Snytax:

;WITH CTEName(col1, col2,col3…..coln) AS

( 
SELECT col1, col2,col3…..coln (followed by conditions)
)

To view the CTE:

Select col1, col2,col3…..coln from CTEName

Let’s we see some more example

We can delete duplicate rows in a table using CTE

Here we have taken a table called ‘EmpDtl‘

id     Name              Deptid
———————————-
1      Pritam                  1
2      Rintu Das             1
3      Lisa Das               2
4      Piyali Das             2
5      Subhadip Das      2
6      Anurag D             2
7      Pritam                  1
8      Pritam                  1
9      Pritam                  1

In this table we can see the duplicate record named ‘Pritam‘

Now we will go for solution to remove duplicate

With cte_duplicateDetete (id,Name,Deptid,Rowrank)
as (
select id,Name,Deptid, 
row_number()over(partition by Name,Deptid order by name,Deptid)as Rowrank from EmpDtl
)

Now we can get Ouput like this:

id     Name              Deptid    Rowrank
————————————————-
1      Pritam                  1            1
2      Rintu Das             1            1
3      Lisa Das               2            1
4      Piyali Das             2            1
5      Subhadip Das      2            1
6      Anurag D             2             1
7      Pritam                  1             2
8      Pritam                  1             3
9      Pritam                  1             4

Now we have to run the query given below

delete from cte_duplicateDetete where rownumber<>1 (Instade of ‘<> 1′, we can use ‘<1′
also)

Now the duplicate records got removed from ‘EmpDtl‘

Select * from EmpDtl

Output:

id      Name       Deptid
—————————
1       Pritam           1
2       Rintu Das      1
3       Lisa Das        2
4       Piyali Das      2
5       Subhadip..     2
6       Anurag D       2