CTE (Common Table Expression)

Posted: September 14, 2010 in SQL Server 2005

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

-- Create temp table for products
CREATE TABLE #tmp_prod
            (prod_id    NUMERIC(18,0),
             prod_name  VARCHAR(100),
             prod_proce NUMERIC(18,2))

-- Insert values into temp table
INSERT INTO #tmp_prod (prod_id,prod_name,prod_proce) VALUES (1, 'First Product',  10.00);
INSERT INTO #tmp_prod (prod_id,prod_name,prod_proce) VALUES (2, 'Second Product', 20.00);
INSERT INTO #tmp_prod (prod_id,prod_name,prod_proce) VALUES (3, 'Third Product',  30.00);
INSERT INTO #tmp_prod (prod_id,prod_name,prod_proce) VALUES (4, 'Fourth Product', 40.00);
INSERT INTO #tmp_prod (prod_id,prod_name,prod_proce) VALUES (5, 'Fifth Product',  50.00);

-- Select results from product table
SELECT * FROM #tmp_prod;

-- Declare CTE for products
WITH cte_prod(prod_id, prod_name, prod_price)
AS
(
   SELECT 1, 'First Product',  10.00
   UNION
   SELECT 2, 'Second Product', 20.00
   UNION
   SELECT 3, 'Third Product',  30.00
)

-- Use CTE in a query
SELECT t.*
FROM   #tmp_prod           t
       INNER JOIN cte_prod c ON c.prod_id = t.prod_id;

-- Drop temp table
DROP TABLE #tmp_prod;
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