CTE values need to insert into another table-CodePudding

Below query i am not able to insert common table expression select values into another table.

;WITH cte AS
(
    SELECT 
        rd.reorderhistoryId,
        rd.dateCreated,
        rd.personId,
        rd.reorderId,
        rd.statusNo,
        rd.createdBy,
        r.OrganizationId
    FROM 
        (SELECT 
             MIN(reorderhistoryId) AS reorderhistoryId,
             MIN(personId) AS personId,
             reorderId
         FROM 
             reorderHistoryDetails
         GROUP BY 
             reorderId) rh 
    INNER JOIN 
        reorderHistoryDetails rd ON rd.reorderhistoryId = rh.reorderhistoryId
    INNER JOIN 
        reorderDetails r ON r.personId = rd.personId
    WHERE 
        rd.statusNo = 1059
)
SELECT DISTINCT TOP 5
    rlst.personId AS personId,
    'Start Reorder Process' AS reorderStatusType,
    1 AS productId,
    3 AS amountPaid,
    rlst.reorderId,
    rlst.OrganizationId AS orgId,
    rlst.createdBy,
    rlst.dateCreated,
    rlst.dateCreated AS timeStamp
FROM
    cte rlst
INNER JOIN 
    doctororderall d ON rlst.personId = d.personId
                     AND d.productId = 1
                     AND YEAR(rlst.dateCreated) = YEAR(d.dateCreated)
INNER JOIN 
    patientdetails pd ON pd.personId = d.personId
WHERE 
    rlst.datecreated > '2020-09-01'
    AND rlst.dateCreated <= '2021-10-06'

INSERT INTO rouletteTracking (personId, reorderStatusType, productId,
                              amountPaid, reorderId, orgId, 
                              createdBy, dateCreated, timeStamp)
SELECT *
FROM cte

I'm getting this error:

Msg 208, Level 16, State 1, Line 14
Invalid object name 'cte'.

CodePudding user response：

Both myself (in the comments) and Grant (in their answer) has already covered this, however, to repeat this a Common Table Expression is an expression. It's scope is limited to the scope of the statement it is define in (like other expressions). If you properly terminate your statements you'll see you have two statements above; a SELECT and an INSERT, and thus the CTE isn't defined in the second:

WITH cte AS
    (SELECT rd.reorderhistoryId,
            rd.dateCreated,
            rd.personId,
            rd.reorderId,
            rd.statusNo,
            rd.createdBy,
            r.OrganizationId
     FROM (SELECT MIN(reorderhistoryId) AS reorderhistoryId,
                  MIN(personId) AS personId,
                  reorderId
           FROM reorderHistoryDetails
           GROUP BY reorderId) rh --on d.personId=rh.personId
          INNER JOIN reorderHistoryDetails rd ON rd.reorderhistoryId = rh.reorderhistoryId
          INNER JOIN reorderDetails r ON r.personId = rd.personId
     WHERE rd.statusNo = 1059)
SELECT DISTINCT TOP 5
       rlst.personId AS personId,
       'Start Reorder Process' AS reorderStatusType,
       1 AS productId,
       3 AS amountPaid,
       rlst.reorderId,
       rlst.OrganizationId AS orgId,
       rlst.createdBy,
       rlst.dateCreated,
       rlst.dateCreated AS timeStamp
FROM cte rlst
     INNER JOIN doctororderall d ON rlst.personId = d.personId
                                AND d.productId = 1
                                AND YEAR(rlst.dateCreated) = YEAR(d.dateCreated)
     INNER JOIN patientdetails pd ON pd.personId = d.personId
WHERE rlst.datecreated > '2020-09-01'
  AND rlst.dateCreated <= '2021-10-06'; --This statement ends HERE

--New statement starts here. The CTE cte has no context here.
INSERT INTO rouletteTracking (personId,
                              reorderStatusType,
                              productId,
                              amountPaid,
                              reorderId,
                              orgId,
                              createdBy,
                              dateCreated,
                              timeStamp)
SELECT *
FROM cte;

Assuming you want to SELECT the TOP (5) (arbitrary) rows first from the CTE first, and then INSERT the entire result set from the CTE into your table afterwards, I would INSERT the data into a temporary table first, and then SELECT and INSERT from that:

WITH cte AS
    (SELECT rd.reorderhistoryId,
            rd.dateCreated,
            rd.personId,
            rd.reorderId,
            rd.statusNo,
            rd.createdBy,
            r.OrganizationId
     FROM (SELECT MIN(reorderhistoryId) AS reorderhistoryId,
                  MIN(personId) AS personId,
                  reorderId
           FROM reorderHistoryDetails
           GROUP BY reorderId) rh --on d.personId=rh.personId
          INNER JOIN reorderHistoryDetails rd ON rd.reorderhistoryId = rh.reorderhistoryId
          INNER JOIN reorderDetails r ON r.personId = rd.personId
     WHERE rd.statusNo = 1059)
SELECT *
INTO #Temp
FROM cte;

SELECT DISTINCT TOP 5
       rlst.personId AS personId,
       'Start Reorder Process' AS reorderStatusType,
       1 AS productId,
       3 AS amountPaid,
       rlst.reorderId,
       rlst.OrganizationId AS orgId,
       rlst.createdBy,
       rlst.dateCreated,
       rlst.dateCreated AS timeStamp
FROM #Temp rlst
     INNER JOIN doctororderall d ON rlst.personId = d.personId
                                AND d.productId = 1
                                AND YEAR(rlst.dateCreated) = YEAR(d.dateCreated)
     INNER JOIN patientdetails pd ON pd.personId = d.personId
WHERE rlst.datecreated > '2020-09-01'
  AND rlst.dateCreated <= '2021-10-06'
ORDER BY {The Column to order by}; --This statement ends HERE

--New statement starts here. The CTE cte has no context here.
INSERT INTO rouletteTracking (personId,
                              reorderStatusType,
                              productId,
                              amountPaid,
                              reorderId,
                              orgId,
                              createdBy,
                              dateCreated,
                              timeStamp)
SELECT *
FROM #Temp;

Note: I expect the INSERT statement at the end to still fail. You attempt to insert into a timestamp column (a deprecated synonym for rowversion); that isn't allowed.

CodePudding user response：

The name Common Table Expression is a bit misleading. Many people read it and see the word "TABLE" very prominently. They then assume that they're dealing with a new kind of temporary table or table variable. However, the word to focus on is "EXPRESSION".

A CTE is only useable within the statement that defines it. It's not, in any way, temporary storage. It's simply a query. It's like having a sub-query to define a table like this:

SELECT * 
FROM (SELECT * FROM dbo.MyTable) as NotaCTEButActsLikeOne

In order to do your process, you need to move the entire SELECT statement including your CTE into a single statement with the INSERT process.

EDIT:

Here. You just move everything so that it's a single statement:

WITH
cte AS
    (SELECT rd.reorderhistoryId,
            rd.dateCreated,
            rd.personId,
            rd.reorderId,
            rd.statusNo,
            rd.createdBy,
            r.OrganizationId
     FROM (SELECT MIN(reorderhistoryId) AS reorderhistoryId,
                  MIN(personId) AS personId,
                  reorderId
           FROM reorderHistoryDetails
           GROUP BY reorderId) rh --on d.personId=rh.personId
          INNER JOIN reorderHistoryDetails rd ON rd.reorderhistoryId = rh.reorderhistoryId
          INNER JOIN reorderDetails r ON r.personId = rd.personId
     WHERE rd.statusNo = 1059)
INSERT INTO rouletteTracking (personId,
                              reorderStatusType,
                              productId,
                              amountPaid,
                              reorderId,
                              orgId,
                              createdBy,
                              dateCreated,
                              timeStamp)
SELECT DISTINCT TOP 5
       rlst.personId AS personId,
       'Start Reorder Process' AS reorderStatusType,
       1 AS productId,
       3 AS amountPaid,
       rlst.reorderId,
       rlst.OrganizationId AS orgId,
       rlst.createdBy,
       rlst.dateCreated,
       rlst.dateCreated AS timeStamp
FROM cte rlst
     INNER JOIN doctororderall d ON rlst.personId = d.personId
                                AND d.productId = 1
                                AND YEAR(rlst.dateCreated) = YEAR(d.dateCreated)
     INNER JOIN patientdetails pd ON pd.personId = d.personId
--inner join (select min(reorderhistoryId) as reorderhistoryId,min(personId) as personId,reorderId from reorderHistoryDetails where statusNo=1059 and personId=226020  group by reorderId )as  rh  on d.personId=rh.personId
--inner join reorderHistoryDetails rd on rd.reorderHistoryId=rh.reorderhistoryId and rd.statusNo=1059 
WHERE rlst.datecreated > '2020-09-01'
  AND rlst.dateCreated <= '2021-10-06';

Also, please, the semi-colon is a statement terminator, not something you put in front of the WITH clause. Examples on line do that so that people can copy the code and it will compile (a righteous approach). However, just putting it at the end of your statements, everything will work and the code won't look awful.