SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT * Into #tempTable


SELECT * Into #tempTable

Author
Message
patelmohamad
patelmohamad
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 765
Hi Guys

What is the difference between

SELECT * INTO #TempTable FROM CustomerMaster WHERE ......
OR

INSERT INTO #TempTable
SELECT * FROM CustomerMaster WHERE ......

Which one is fastest mode or which should i prefer to use....

Thanks

Patel Mohamad
MarkusB
MarkusB
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5943 Visits: 4208
SELECT INTO will create a new temporary table with exactly the same columnnames and datatypes, while in the second case you first have to do a create tabel where you can define alternative columnnames and to a certain degree different datatypes.

I don't think there's a big difference in performance.

Markus Bohse
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 13351
MarkusB (10/11/2011)
I don't think there's a big difference in performance.


SELECT ... INTO can be minimally logged, while INSERT INTO is fully logged.
From a performance standpoint, it makes some difference.

I say it can be minimally logged because is some scenarios it is fully logged. See here for details: http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/15/what-gets-logged-for-select-into.aspx

Hope this helps
Gianluca

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2961 Visits: 1351
I think; before looking at the speed you need to understand the impact it has on the actual table definition.
Collations and datatypes etc will come into play when the temp table is created explicity while they default to the tempdb and the source table when implicit.

Jayanth Kurup
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 13351
Jayanth_Kurup (10/11/2011)
I think; before looking at the speed you need to understand the impact it has on the actual table definition.
Collations and datatypes etc will come into play when the temp table is created explicity while they default to the tempdb and the source table when implicit.


Completely agree.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
MarkusB
MarkusB
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5943 Visits: 4208
Gianluca Sartori (10/11/2011)
MarkusB (10/11/2011)
I don't think there's a big difference in performance.


SELECT ... INTO can be minimally logged, while INSERT INTO is fully logged.
From a performance standpoint, it makes some difference.

I say it can be minimally logged because is some scenarios it is fully logged. See here for details: http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/15/what-gets-logged-for-select-into.aspx

Hope this helps
Gianluca


Gianluca,

thanks for pointing it out. I knew that something like that was the case but couldn't find it back in BOL.

Markus Bohse
patelmohamad
patelmohamad
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 765
Thanks guys

and Gianluca thank you once again for posting a article of Kalen Delaney
it gives the details of the Temptable

Patel Mohamad
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 13351
You're welcome.
Glad I could help.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search