Today I have received a fantastic email from Matthew Spieth. SQL Server expert from Ohio. He recently had a great conversation with his colleagues in the office and wanted to make sure that everybody who reads this blog knows about this little feature which is commonly confused.

Here is his statement and we will start our story with Matthew’s own statement: “Users often confuse CTE with Temp Table but technically they both are different, CTE are like Views and they can be updated just like views.“

Very true statement from Matthew. I totally agree with what he is saying. Just like him, I have enough, time came across a situation when developers think CTE is like temp table. When you update temp table, it remains in the scope of the temp table and it does not propagate it to the table based on which temp table is built. However, this is not the case when it is about CTE, when you update CTE, it updates underlying table just like view does.

Here is the working example of the same built by Matthew to illustrate this behavior.

Check the value in the base table first.

USE AdventureWorks2012;
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';


Now let us build CTE with the same data.

;WITH CTEUpd(ProductID, Name, ProductNumber, Color)
AS(
SELECT ProductID, Name, ProductNumber, Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738')


Now let us update CTE with following code.

-- Update CTE
UPDATE CTEUpd SET Color = 'Rainbow';

Now let us check the BASE table based on which the CTE was built.

-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';


That’s it! You can update CTE and it will update the base table.

Here is the script which you should execute all together.

USE AdventureWorks2012;
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';
-- Build CTE
;WITH CTEUpd(ProductID, Name, ProductNumber, Color)
AS(
SELECT ProductID, Name, ProductNumber, Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738')
-- Update CTE
UPDATE CTEUpd SET Color = 'Rainbow';
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';


If you are aware of such scenario, do let me know and I will post this on my blog with due credit to you.

Reference: Pinal Dave (http://blog.sqlauthority.com)