Post reply

SELECT * Into #tempTable

  • October 11, 2011 at 1:13 am

    #104097

    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

  • October 11, 2011 at 1:24 am

    #1392686

    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.

    [font="Verdana"]Markus Bohse[/font]

  • October 11, 2011 at 2:24 am

    #1392707

    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

  • October 11, 2011 at 2:32 am

    #1392714

    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[/url]

  • October 11, 2011 at 2:40 am

    #1392721

    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

  • October 11, 2011 at 3:14 am

    #1392732

    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.

    [font="Verdana"]Markus Bohse[/font]

  • October 12, 2011 at 12:21 am

    #1393236

    Thanks guys

    and Gianluca thank you once again for posting a article of Kalen Delaney

    it gives the details of the Temptable

    Patel Mohamad

  • October 12, 2011 at 2:07 am

    #1393279

    You're welcome.

    Glad I could help.

    -- Gianluca Sartori

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply