SELECT INTO with subquery

sql-server subquery common-table-expression select-into
Kenny picture Kenny · Sep 15, 2015 · Viewed 7.6k times · Source

Regarding SELECT INTO in SQL Server

The following throw an error Incorrect syntax near ')'.

SELECT * INTO Sales.MyTable FROM 
(SELECT TOP(100) * FROM Sales.Customer)

The following will pass

With tempCust AS
(
SELECT TOP(100) * FROM Sales.Customer
)
SELECT * INTO Sales.MyTable FROM tempCust

What is the rule behind that ?

Answer

Amnesh Goel picture Amnesh Goel · Sep 15, 2015

Can you add an alias to your subquery like shown below and then give it a try..

SELECT * INTO Sales.MyTable FROM 
(SELECT TOP(100) * FROM Sales.Customer) as abc

Related questions

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

In this excellent SO question, differences between CTE and sub-queries were discussed. I would like to specifically ask: In what circumstance is each of the following more efficient/faster? CTE Sub-Query Temporary Table Table Variable Traditionally, I've used lots of …

Read More
Difference between CTE and SubQuery?

From this post How to use ROW_NUMBER in the following procedure? There are two versions of answers where one uses a sub-query and the other uses a CTE to solve the same problem. Now then, what is the advantage …

Read More
Nested select statement in SQL Server

Why doesn't the following work? SELECT name FROM (SELECT name FROM agentinformation) I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as SELECT name FROM agentinformation Doesn't the inner select …

Read More