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.

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

Now let us build CTE with the same data.

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

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s