SQL 2008 R2 CTE syntax error in a SELECT statement

Question

This question is based on my previous question

SQL server 2008 R2, select one value of a column for each distinct value of another column

about CTE on SQL server 2008.

   WITH my_cte(id_num, rn) AS (
      SELECT name, 
      rn = ROW_NUMBER() OVER (PARTITION BY a.name ORDER BY newid())
            FROM my_table as a
      )
   SELECT id_num FROM my_cte WHERE rn = 1

   INSERT INTO #temp_table 
   SELECT a.address from another_table as a,
            id_num from my_cte  -- here, I got error!!!

Why I got error: Incorrect syntax near the keyword 'from'.

I need to get a new table with one column from another_table and one column from my_cte.

e.g.

 address (from another_table)    id_num (from my_cte)
  city_1                        65 
  city_1                        36
  city_2                        65
  city_2                        36
  city_3                        65
  city_3                        36

What kinds of join I should use to get the above table so that each address is associated with all id_num from the CTE ? Suppose that id_num has only 65 and 36 two values. my_cte has no column of address.

Any help would be appreciated.

Answer 1

The CTE only exists for a single query. But you can use it with insert as well as select:

   WITH my_cte(id_num, rn) AS (
      SELECT name, ROW_NUMBER() OVER (PARTITION BY a.name ORDER BY newid()) as rn
      FROM my_table as a
   )
   INSERT INTO #temp_table 
       SELECT a.address, id_num
       from another_table as a JOIN
            my_cte
            on a.name = my_cte.name;

Answer 2

A CTE can only be used in the statement directly after it. After you do SELECT id_num FROM my_cte WHERE rn = 1, the cte no longer exists.