Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT * Into #tempTable Expand / Collapse
Author
Message
Posted Tuesday, October 11, 2011 1:13 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
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
Post #1188235
Posted Tuesday, October 11, 2011 1:24 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:13 AM
Points: 4,432, Visits: 4,167
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
Post #1188238
Posted Tuesday, October 11, 2011 2:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1188262
Posted Tuesday, October 11, 2011 2:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
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
Post #1188273
Posted Tuesday, October 11, 2011 2:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1188280
Posted Tuesday, October 11, 2011 3:14 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:13 AM
Points: 4,432, Visits: 4,167
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
Post #1188294
Posted Wednesday, October 12, 2011 12:21 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
Thanks guys

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


Patel Mohamad
Post #1188884
Posted Wednesday, October 12, 2011 2:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 5,014, Visits: 10,514
You're welcome.
Glad I could help.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1188933
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse