SELECT INTO with subquery

Question

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 ?

`SELECT * INTO Sales.MyTable FROM (SELECT TOP(100) * FROM Sales.Customer) AS T` You missed the alias of subquery result — Deep, Sep 15 '15 at 10:44
What's wrong with just: SELECT TOP(100) * INTO Sales.MyTable FROM Sales.Customer — Brian Stork, Sep 15 '15 at 14:40
Indeed the subquery or CTE is not needed in this case. Initially I wanted to do something that needed a CTE, e.g. Select top 2 rows for each partition partitioned by customerID. Errors occured and I reduce it to a simpler example for debugging. — Kenny, Sep 16 '15 at 10:14

Amnesh Goel · Answer 1 · 2015-09-15T11:59:56.263

10

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

edited Sep 15 '15 at 11:59

answered Sep 15 '15 at 10:48

Amnesh Goel

Spot on. Yet I am not convinced with the 'why'. For self-join (A join A) I can understand. But normally when it's not ambiguous, I tend to skip it and usually there is no problem. – Kenny Sep 15 '15 at 10:57
Besides, regarding [your link](https://technet.microsoft.com/en-us/library/ms190410(v=sql.105).aspx), why do we need to use join or subqueries. A test with `SELECT StateProvinceID, AddressID FROM Person.Address WHERE StateProvinceID = 39` gives me the same result – Kenny Sep 15 '15 at 10:59

1 Answers1