question

Bhupendra99 avatar image
Bhupendra99 asked

Create Table Vs Select * Into

What would be better to Use when creating a Temp table Create Table or Select * Into (Please Note that the newly created temp table would have 20 or More columns) that is should we use Select * Into or should we properly create a Table which will be better when considering from performance point of view
temporary-table
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
Mart avatar image
Mart answered
Hi Bhupendra99 It's my understanding that both would be created equally and hence would have the same performance implications. What I'd personally recommend depends on how you're going to use the code. If it's going anywhere 'proper' then create the table explicitly and inset into it. It will be more readable and will handle future code changes more easily. If you're writing a one off query that doesn't need the benefits of the above then you can save a little time by selecting into the temp table. If you do this ensure you have comments at the top to say what it's doing and why. Remember you can always set up a test to prove things to yourself and justify your reasoning. Personally I'd explicitly create the table most of the time, ensuring the datatypes are the same.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Only thing I would add is that a SELECT ... INTO actually runs in 2 parts: 1. Query is inspected to determine the datatypes from the source tables, and table is created 2. Rows are inserted into the temp table Importantly there are no SCHEMA STABILITY locks taken taken so if any schema changes happen in the very small window between steps 1 and 2, a SQL error 539 is thrown > Msg 539, Level 16, State 1, Line 1 > Schema changed after the target table was created. Rerun the Select Into query. You can prevent this happening by explicitly creating the temp table first and then inserting rows.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mart avatar image Mart commented ·
Thanks for adding that, nice piece of information.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.