CODEWITHSUNDEEP
sqlsql-server
user2961127
user2961127

Reputation: 1143

How to store query result into temporary table

I am trying to insert the results generated from SQL query into temporary table using following query:

SELECT * 
INTO #YourTempTable
FROM
    (WITH cte AS 
     (
        SELECT
            sb.case, sb.Trace, sb.Amount, sp.edge, sp.UserId,
            count(*) over (partition by sp.edge) as cnt
        FROM 
            Budget sb 
        JOIN
            Sap sp ON sb.Trace = sp.Trace
        WHERE 
            sb.Trace IN (SELECT Trace FROM Sap 
                         WHERE sb.UserId in ('R5', 'HB'))
     )
     SELECT cte.*
     FROM cte
     WHERE cnt > 1
     ORDER BY edge DESC;
)

I'm getting error:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'.

Msg 319, Level 15, State 1, Line 4
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 12
Incorrect syntax near ')'.

Can anyone please let me know about this?

Upvotes: 0

Views: 4546

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

WITH goes before SELECT:

with cte as (
      select sb.case, sb.Trace, sb.Amount, sp.edge, sp.UserId,
             count(*) over (partition by sp.edge) as cnt
      from Budget sb join
           Sap sp 
           on sb.Trace = sp.Trace
      where sb.Trace in (select Trace from Sap where sb.UserId in ('R5', 'HB'))
     )
SELECT * 
INTO #YourTempTable
FROM cte
WHERE cnt > 1
ORDER BY edge desc;

As a note: your syntax is supported by Oracle, but not by other databases. In general, the WITH should be at the very beginning of the statement.

Also, the subquery is unnecessary. In fact, you would get two additional errors: the subquery has no alias and ORDER BY is not allowed in subqueries (unless TOP or FETCH FIRST is used).

Upvotes: 7

Related Questions