Use Results of CTE to Update Another Table

Question

I have the following CTE that does what I want it to do.

;WITH numbering AS 
(
    SELECT SrcID, AsOfDate, PID,
           dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
    FROM   RAW_DATA
)
SELECT SrcID,
       MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
       MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
       MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
       MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
FROM   numbering
GROUP BY SrcID

I need to use that, as well as the AsOfDate and PID, but I don’t want to display these in the CTE, as that throws off populating PID1, PID2, PID3, and PID4 . . . all of which is correct now. I do need the SrcID and AsOfDate to do an update to another table, named ‘RAW_DATA’. How can I run the CTE to generate the specific data set that I need, and then do an update to the RAW_DATA table, based on joins between SrcID and AsOfDate?

I think it should be something like this:

    ;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,
               dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )
    SELECT SrcID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    FROM   numbering
    GROUP BY SrcID
    
INSERT INTO RAW_DATA(SrcID, AsOfDate, PID, PID1, PID2, PID3, PID4)
Select *
FROM         RAW_DATA INNER JOIN numbering 
             ON RAW_DATA.SrcID = numbering.SrcID 
             AND RAW_DATA.AsofDate = numbering.AsofDate

However, that throws this error: Invalid object name 'numbering'. I am on SQL Server 2008.

Update

Modifying my original post just a bit here.

Jeffrey, I'm testing your solution:

--drop table Count_Unique_PID
;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,PID1,PID2,PID3,PID4,
               dense_rank() OVER (PARTITION BY AsOfDate, SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )

    SELECT SrcID,AsOfDate, PID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    INTO Count_Unique_PID
    FROM   numbering
    GROUP BY SrcID,AsOfDate, PID

    SELECT SrcID,
           AsOfDate,
           PID,
           PID1,
           PID2,
           PID3,
           PID4
    FROM   Count_Unique_PID
    GROUP BY SrcID,AsOfDate, PID,PID1,PID2,PID3,PID4


UPDATE    RAW_DATA
SET              PID1 = B.PID1, 
                 PID2 = B.PID2,
                 PID3 = B.PID3,
                 PID4 = B.PID4
FROM         RAW_DATA AS A INNER JOIN Count_Unique_PID  As B
             ON A.SrcID = B.SrcID 
             AND A.AsofDate = B.AsofDate

This runs, but it blows up my rows from 357,518 to 724,150. The number of records should stay the same; it should remain 357,518 after the Update is done...something is still not quite right here. Maybe I am missing a Group By somewhere, or something like that. I don't see what the actual problem is. Any additional thoughts on this?

Answer 1

Your SELECT statement is doing nothing for the insert (maybe it's for validation). Simply remove the select statement and your insert should work.

;WITH numbering AS 
(
    SELECT SrcID, AsOfDate, PID,
           dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
    FROM   RAW_DATA
)


INSERT INTO RAW_DATA(SrcID, AsOfDate, PID, PID1, PID2, PID3, PID4)
Select *
FROM         
         RAW_DATA INNER JOIN numbering 
         ON RAW_DATA.SrcID = numbering.SrcID 
         AND RAW_DATA.AsofDate = numbering.AsofDate

If you need to run a select on the CTE as well, you should use a temp table to it will exist as long as the query is running.

Answer 2

You need encapsulate your query as a second cte:

sintaxis is

WITH cte1 as (
   SELECT ...
), cte2  as (
   SELECT *
   FROM cte1
   .....
)
INSERT INTO table_name
SELECT * 
FROM cte2

Answer 3

CTEs can only be referenced by the immediately-following statement. If you need the results later, you could insert the CTE into a temp table, then select from that temp table and then insert from that temp table:

;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,
               dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )

    SELECT SrcID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    INTO #tmp
    FROM   numbering
    GROUP BY SrcID

    SELECT SrcID,
           PID1,
           PID2,
           PID3,
           PID4
    FROM   #tmp
    GROUP BY SrcID

INSERT INTO RAW_DATA(SrcID, AsOfDate, PID, PID1, PID2, PID3, PID4)
Select *
FROM         RAW_DATA INNER JOIN #tmp
             ON RAW_DATA.SrcID = #tmp.SrcID 
             AND RAW_DATA.AsofDate = #tmp.AsofDate