SQL error in subquery

Question

The following code

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
        DROP TABLE #temp

SELECT *
INTO #temp
FROM (SELECT * FROM tbl)

Gives me a syntax error right after last parenthesis

I don't understand why at all

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Sep 14, 2015 at 16:31

Lukasz Szozda · Accepted Answer · 2015-09-14 16:34:59Z

3

You don't need subquery:

SELECT * 
INTO #temp
FROM tbl;

Or add alias:

SELECT t.*
INTO #temp
FROM (SELECT * FROM tbl) AS t

You can also use CTE for more complex subqueries:

;WITH cte AS
(
   SELECT *
   FROM tbl
)
SELECT cte.*
INTO #temp
FROM cte;

answered Sep 14, 2015 at 16:13

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wel that isn't the whole code actually the select * from tbl is actually a sub query

@Kamster You need to add alias

Vamsi Prabhala · Accepted Answer · 2015-09-14 16:14:24Z

2

The SELECT * FROM tbl should have an alias defined.

SELECT t.*
INTO #temp
FROM (SELECT * FROM tbl) t

answered Sep 14, 2015 at 16:14

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges