one difference b/w CTE and Temp table

CTE can be Updated

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.

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.

FROM [Production].[Product]
WHERE ProductNumber 'CA-6738';

Now let us build CTE with the same data.

;WITH CTEUpd(ProductIDNameProductNumberColor)
SELECT ProductIDNameProductNumberColor
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
FROM [Production].[Product]
WHERE ProductNumber 'CA-6738';

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s