SQL Select Into #Temp

Question

I have a complex SQL query that works. It's like

    SELECT * FROM Site s
    JOIN (
        SELECT DISTINCT z.Value FROM Doc z
        JOIN (
            SELECT x.DocumentID FROM Doc x
            JOIN (
                SELECT DocumentID, MAX(VERSION) AS VERSION 
                FROM Doc GROUP BY DocumentID) y ON y.DocumentID = x.DocumentID
            WHERE DocumentTypeID = 78 AND MetadataTypeID = 22 AND VALUE > GETDATE() AND y.Version = x.Version
            ) a ON z.DocumentID = a.DocumentID
        WHERE MetadataTypeID = 2
    ) b ON b.Value = s.SiteID
    WHERE SiteID > 0

It does what I want it to do. But when I wrap it in

SELECT * INTO #Temp FROM ()

I get an incorrect syntax near ')'.

I'm confused. I just want to put the results into a temp table so I can do further work on it. Why doesn't it work?

use an alias, `SELECT * INTO #Temp FROM () AS A` – Lamak Nov 03 '14 at 18:00 — Lamak, Nov 03 '14 at 18:00
try `SELECT * INTO #Temp FROM () AS a` – RBarryYoung Nov 03 '14 at 18:00 — RBarryYoung, Nov 03 '14 at 18:00
Nice. Thanks for the quick answers guys. – erosebe Nov 03 '14 at 18:01 — erosebe, Nov 03 '14 at 18:01

score 2 · Accepted Answer · answered Nov 03 '14 at 18:03

You're missing an alias for subquery.

SELECT * INTO #MyTempTable
FROM
(
SELECT * FROM Site s
    JOIN (
        SELECT DISTINCT z.Value FROM Doc z
        JOIN (
            SELECT x.DocumentID FROM Doc x
            JOIN (
                SELECT DocumentID, MAX(VERSION) AS VERSION 
                FROM Doc GROUP BY DocumentID) y ON y.DocumentID = x.DocumentID
            WHERE DocumentTypeID = 78 AND MetadataTypeID = 22 AND VALUE > GETDATE() AND y.Version = x.Version
            ) a ON z.DocumentID = a.DocumentID
        WHERE MetadataTypeID = 2
    ) b ON b.Value = s.SiteID
    WHERE SiteID > 0
) AS DT

score 1 · Answer 2 · answered Nov 03 '14 at 18:02

select * into TBL_name from
( SELECT * FROM Site s
    JOIN (
        SELECT DISTINCT z.Value FROM Doc z
        JOIN (
            SELECT x.DocumentID FROM Doc x
            JOIN (
                SELECT DocumentID, MAX(VERSION) AS VERSION 
                FROM Doc GROUP BY DocumentID) y ON y.DocumentID = x.DocumentID
            WHERE DocumentTypeID = 78 AND MetadataTypeID = 22 AND VALUE > GETDATE() AND y.Version = x.Version
            ) a ON z.DocumentID = a.DocumentID
        WHERE MetadataTypeID = 2
    ) b ON b.Value = s.SiteID
    WHERE SiteID > 0)al

SQL Select Into #Temp

2 Answers2