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 ?
You can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed. For instance, you can use a subquery as one of the column expressions in a SELECT list or as a table expression in the FROM clause.
A subquery is a SELECT statement embedded in another SQL statement, such as a SELECT, INSERT, DELETE, or UPDATE statement. The set of value(s) returned by the inner SELECT statement are passed to the outer SQL statement. The inner SELECT statement is always embraced in parentheses.
Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date or number functions.
The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With