INSERT INTO WITH Common Table Expression - SQL Server

Question

I have figured out how to recursively find all employees reporting up to a certain manager using Common Table Expressions (Thanks to StackOverflow!).

This is the code that works for me:

WITH MyCTE AS 
(
    SELECT [WWID] FROM [x500]..[WorkerPublicExtended] 
    WHERE [MgrWWID] = '10624529' AND ([StatCode] = 'A') AND ([BadgeType] = 'BB') 
    UNION ALL 
    SELECT [WorkerPublicExtended].[WWID] FROM [x500]..[WorkerPublicExtended] 
        INNER JOIN MyCTE ON [WorkerPublicExtended].[MgrWWID] = MyCTE.WWID 
    WHERE [WorkerPublicExtended].[MgrWWID] IS NOT NULL 
        AND ([BadgeType] = 'BB') AND ([StatCode] = 'A')
) 

SELECT *, 'MGR+10624529' AS [source] FROM MyCTE

This works perfectly. But if I try to insert this into another table (which is the ultimate goal) I can't find any syntactical variation of this code that doesn't throw one or more errors. Can someone help me put these together?

INSERT INTO [LTDtraining].[dbo].[pop00001]
WITH MyCTE AS 
(
    SELECT [WWID] FROM [x500]..[WorkerPublicExtended] 
    WHERE [MgrWWID] = '10624529' AND ([StatCode] = 'A') AND ([BadgeType] = 'BB') 
    UNION ALL 
    SELECT [WorkerPublicExtended].[WWID] FROM [x500]..[WorkerPublicExtended] 
        INNER JOIN MyCTE ON [WorkerPublicExtended].[MgrWWID] = MyCTE.WWID 
    WHERE [WorkerPublicExtended].[MgrWWID] IS NOT NULL 
        AND ([BadgeType] = 'BB') AND ([StatCode] = 'A')
) 

SELECT *, 'MGR+10624529' AS [source] FROM MyCTE

It is throwing errors like:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'MyCTE'.

Or the ever-popular

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

And if I insert the suggested semicolon, it responds with

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ';'.

So it makes me think I don't know what I'm doing here, and neither does SQL.

Try putting the insert statement after the CTE (e.g 'WITH CTE AS (...) INSERT...' — ZLK, Dec 22 '15 at 23:24

John Odom · Accepted Answer · 2015-12-22T23:31:11.770

5

The error can easily be fixed by moving the INSERT statement down below the WITH like so:

WITH MyCTE AS 
(
    SELECT [WWID] FROM [x500]..[WorkerPublicExtended] 
    WHERE [MgrWWID] = '10624529' AND ([StatCode] = 'A') AND ([BadgeType] = 'BB') 
    UNION ALL 
    SELECT [WorkerPublicExtended].[WWID] FROM [x500]..[WorkerPublicExtended] 
        INNER JOIN MyCTE ON [WorkerPublicExtended].[MgrWWID] = MyCTE.WWID 
    WHERE [WorkerPublicExtended].[MgrWWID] IS NOT NULL 
        AND ([BadgeType] = 'BB') AND ([StatCode] = 'A')
)

INSERT INTO [LTDtraining].[dbo].[pop00001] 
SELECT *, 'MGR+10624529' AS [source] FROM MyCTE

edited Dec 22 '15 at 23:31

answered Dec 22 '15 at 23:25

John Odom

1,189
2
20
35

3

The semicolon should go to the **end** of each statement: https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons – Dec 22 '15 at 23:25
1

@a_horse_with_no_name I agree, but it's hard to get into the habit of doing that lol. – John Odom Dec 22 '15 at 23:26
2

By sticking to this way of writing it, people think that a CTE is started using the keyword `;WITH` which leads to questions like this: http://stackoverflow.com/q/23078215/330315 – Dec 22 '15 at 23:28
1

@a_horse_with_no_name True, but since we don't know if Richard's code had any code being called before it (or it's probably safe to assume no since he said the top query worked for him), I added the semi-colon for safety precautions. I'll just remove it and remember to add the semicolon at the end next time :). – John Odom Dec 22 '15 at 23:30
2

Thank you very much Mr. Odom! That did the trick! Very pleased! :-) – Richard Crowley Dec 23 '15 at 00:18

INSERT INTO WITH Common Table Expression - SQL Server

1 Answers1