creating a permanent table from  the results of a select statement in sql server 2016

blossompark
blossompark used Ask the Experts™
on
Hi,
I have a SELECT statement that returns  a result set, I now want to create a table from the results set.
I ththought
SELECT INTO newtable
FROM
(
Select statement
)
would work but it doesnt...
any help appreciated, thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Nitin SontakkeDeveloper
Commented:
The correct syntax is as follows, did you try that?

SELECT *
INTO destinationTable -- This table will be created for you. Exactly same structure as sourceTable. Data types same. No defaults / constraints created.
FROM sourceTable

Open in new window

blossompark

Author

Commented:
Hi Nithin, can the sourceTable be a SELECT statement? Ive tried this but gets an error
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Yes, it can. You get a table with types and lengths derived from the SELECT results, so those might be a little off for you.
SELECT * INTO newtable
FROM
(
   Select statement
) data

Open in new window

Remember: When using from (select ...) in MSSQL you always need to assign column aliases for expressions, and give the resulting "table" an alias too (which is data here).
Nitin SontakkeDeveloper
Commented:
Sure...why not! Just enclose the statement in brackets and give an alias. Always keep on posting errors for clarity.
blossompark

Author

Commented:
Thanks for your assistance, I had left out the alias, that was the issue

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial