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

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
blossomparkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
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

blossomparkAuthor Commented:
Hi Nithin, can the sourceTable be a SELECT statement? Ive tried this but gets an error
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nitin SontakkeDeveloperCommented:
Sure...why not! Just enclose the statement in brackets and give an alias. Always keep on posting errors for clarity.
blossomparkAuthor Commented:
Thanks for your assistance, I had left out the alias, that was the issue
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.