SQL-Queries-CTE

 

Contents 1 Common Table Expression  - CTE – Overview 1 2 CTE with SELECT 2 3 CTE with INSERT 2 3.1 Entire table based on query 2 4 CTE With Update 3 5 CTE with Delete 3 6 CTE with Merge 4 7 General 4 7.1 Multiple CTEs in a Single Query 4 7.2 need to find the 19th value by using Row_Number() 5

1. Common Table Expression  - CTE – Overview

• CTEs offer a logical and legible approach to writing queries that can break the complex queries down to a series of logical steps, help to improve the legibility of your queries, and achieve more complex result sets.

• By pure definition, a CTE is a 'temporary named result set'. In practice, a CTE is a result set that remains in memory for the scope of a single execution of a SELECT, INSERT, UPDATE, DELETE, or MERGE statement.

WITH <common_table_expression> ([column names])

AS

(

   <cte_query_definition>

)

<operation>

2. CTE with SELECT

use WideWorldImporters;

GO

WITH C AS(

SELECT SC.CustomerName

  ,CT.CustomerTransactionID

  ,TransactionAmount

FROM [Sales].[CustomerTransactions] CT

INNER JOIN [Sales].[Customers] SC ON CT.CustomerID = SC.CustomerID 

)

SELECT CustomerName

  ,CustomerTransactionID

  ,TransactionAmount

FROM C

ORDER BY CustomerTransactionID;

3. CTE with INSERT 

1. Entire table based on query

use WideWorldImporters;

GO

WITH C AS(

SELECT SC.CustomerName

  ,CT.CustomerTransactionID

  ,TransactionAmount

FROM [Sales].[CustomerTransactions] CT

INNER JOIN [Sales].[Customers] SC ON CT.CustomerID = SC.CustomerID 

)

SELECT CustomerName

  ,CustomerTransactionID into tmpTable2bDeleted

FROM C

ORDER BY CustomerTransactionID;

4. CTE With Update

use WideWorldImporters;

GO

WITH C AS(

SELECT SC.CustomerName

  ,CT.CustomerTransactionID

  ,TransactionAmount

  ,SC.CustomerID

FROM [Sales].[CustomerTransactions] CT

INNER JOIN [Sales].[Customers] SC ON CT.CustomerID = SC.CustomerID 

)

UPDATE  C SET CustomerName = CustomerName + '1'

where c.CustomerID = 1060

select CustomerName   FROM [Sales].[Customers] where CustomerID = 1060 

5. CTE with Delete

WITH MissingAddress_CTE

AS (

   SELECT *

   FROM dbo.DimCustomerBak

   WHERE AddressLine2 IS NULL

   )

DELETE

FROM MissingAddress_CTE

6. CTE with Merge

WITH SourceTableCTE AS

(

    SELECT * FROM SourceTable

)

MERGE  

 TargetTable AS target

USING SourceTableCTE AS source  

ON (target.PKID = source.PKID)

WHEN MATCHED THEN     

    UPDATE SET target.ColumnA = source.ColumnA

WHEN NOT MATCHED THEN

    INSERT (ColumnA) VALUES (Source.ColumnA);

7. General 

1. Multiple CTEs in a Single Query

- You can write as many as you want and separate them with a comma:

;WITH GroupAlmondDates AS(

  SELECT 

    YEAR(AlmondDate) AlmondYear

    , AlmondDate

    , AlmondValue

    , (AlmondValue*10) Base10AlmondValue

  FROM tbAlmondData

), GetAverageByYear AS(

  SELECT 

    AlmondYear

    , AVG(AlmondValue) AvgAlmondValueForYear

  FROM GroupAlmondDates

  GROUP BY AlmondYear

)

SELECT t.AlmondDate

  , (t.AlmondValue - tt.AvgAlmondValueForYear) ValueDiff

FROM GroupAlmondDates t

  INNER JOIN GetAverageByYear tt ON t.AlmondYear = tt.AlmondYear

2. need to find the 19th value by using Row_Number()

;WITH GroupAlmondDates AS(

  SELECT 

    ROW_NUMBER() OVER (ORDER BY AlmondDate ASC) DateAscId

    , YEAR(AlmondDate) AlmondYear

    , AlmondDate

    , AlmondValue

    , (AlmondValue*10) Base10AlmondValue

  FROM tbAlmondData

)

SELECT *

FROM GroupAlmondDates

WHERE DateAscId = 19