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?

Answer 1

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