Selecting the results of a Common Table Expression after using INSERT INTO

Question

Selecting the results of a Common Table Expression after using INSERT INTO

michael.leach2015

SSCommitted

Points: 1597
More actions
February 7, 2020 at 9:48 pm

#3724083
Here I have a common table expression followed by INSERT INTO. After that, I want to SELECT the results so I can see the newly inserted data, however I get an error. I presume this is because after the first occurrence of SELECT, INSERT INTO, UPDATE or DELETE the CTE can no longer be referenced.
```
With ProductName(ProductID, Description)
AS
(

    SELECT ProductID, ProductName
    FROM dbo.Products

)


INSERT INTO ProductName                     --  You can use INSERT or INSERT INTO.  Either way you get the same result
VALUES
(70, 'Neck and Back Massager'),
(71, 'Goggles'),
(72, 'Back scratcher'),
(73, 'Xylitol Mouthwash')


SELECT *
FROM ProductName;                           --ProductName not recognized since INSERT INTO was already used
```
How can I see the newly inserted data that was inserted into the CTE? I know I can create multiple CTEs with one WITH statement, but I want to reference the same CTE that INSERT INTO is referencing. Is this possible?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

A CTE has an execution scope of a single (the important word) statement that follows the declaration of the CTE. So just for the select, insert, update, delete, etc that follows the CTE. When you have another statement after your insert, the CTE is no longer available. It's just a temporary result set. What your trying to do may be better with a table variable.

Sue

frederico_fonseca SSCoach Points: 16153 More actions · Answer 2

you need to use the output clause

With ProductName(ProductID, Description)
AS
(

    SELECT ProductID, ProductName
    FROM dbo.Products

)


INSERT INTO ProductName                     --  You can use INSERT or INSERT INTO.  Either way you get the same result
output inserted.*

VALUES
(70, 'Neck and Back Massager'),
(71, 'Goggles'),
(72, 'Back scratcher'),
(73, 'Xylitol Mouthwash')

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Or, put the CTE with the select that is calling it:

With ProductName(ProductID, Description)
AS
(

    SELECT ProductID, ProductName
    FROM dbo.Products

)INSERT INTO ProductName                     --  You can use INSERT or INSERT INTO.  Either way you get the same result
VALUES
(70, 'Neck and Back Massager'),
(71, 'Goggles'),
(72, 'Back scratcher'),
(73, 'Xylitol Mouthwash'); -- << Be sure to terminate the statement prior to the CTE


With ProductName(ProductID, Description)
AS
(

    SELECT ProductID, ProductName
    FROM dbo.Products

)
SELECT *
FROM ProductName;

This reply was modified 4 years, 9 months ago by Lynn Pettis.

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 4

frederico_fonseca wrote:

you need to use the output clause

With ProductName(ProductID, Description)
AS
(

    SELECT ProductID, ProductName
    FROM dbo.Products

)


INSERT INTO ProductName                     --  You can use INSERT or INSERT INTO.  Either way you get the same result
output inserted.*

VALUES
(70, 'Neck and Back Massager'),
(71, 'Goggles'),
(72, 'Back scratcher'),
(73, 'Xylitol Mouthwash')

Thank you. I have never used the OUTPUT clause before. I will look into that.

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 5

Lynn Pettis wrote:

Or, put the CTE with the select that is calling it:

With ProductName(ProductID, Description)
AS
(

    SELECT ProductID, ProductName
    FROM dbo.Products

)INSERT INTO ProductName                     --  You can use INSERT or INSERT INTO.  Either way you get the same result
VALUES
(70, 'Neck and Back Massager'),
(71, 'Goggles'),
(72, 'Back scratcher'),
(73, 'Xylitol Mouthwash'); -- << Be sure to terminate the statement prior to the CTE


With ProductName(ProductID, Description)
AS
(

    SELECT ProductID, ProductName
    FROM dbo.Products

)
SELECT *
FROM ProductName;

That's neat. I didn't think you could reuse a CTE name again. I'll try it.