How to use Common Table Expressions In SQL Server

Common Table Expressions offer the same functionality as a view,But you can select,delete,update tables using CTE and you do not have to store definition metadata 

CREATE TABLE #TABLECTE(NAME VARCHAR(20),AGE INT); --Temp Table Creation

INSERT INTO #TABLECTE VALUES('RAFEL',20);
INSERT INTO #TABLECTE VALUES('JAMES',18);
INSERT INTO #TABLECTE VALUES('JOHN',20);
INSERT INTO #TABLECTE VALUES('SYAM',21);
INSERT INTO #TABLECTE VALUES('JACK','');

WITH CTE_Sample (EMP_ID,EMP_NAME,AGE)
AS
(
      SELECT ROW_NUMBER() OVER(ORDER BY NAME), NAME,AGE from #TABLECTE
     
)
--SELECT *  FROM CTE_Sample  --- TO SELECT CTE WITH ROWNUMBER
--WHERE AGE=20

 --UPDATE CTE_Sample  ----  UPDATE #TABLECTE USING  CTE CTE
 --SET AGE=40
 --WHERE EMP_NAME='JACK'

 --DELETE  FROM CTE_Sample --- DELETE #TABLECTE USING CTE
 --WHERE AGE=21 

 SELECT * FROM #TABLECTE ---- SELECT AFTER TABLE OPERATION

DROP TABLE #TABLECTE


No comments:

Post a comment