Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    115

    Unanswered: select into vs insert

    set nocount on
    create table #t1(col_1 char(6),col_2 char(11))
    select getdate()
    select col_1,col_2 into #t2 from #t0
    select getdate()
    insert #t1(col_1,col_2) select col_1,col_2 from #t0
    select getdate()

    ----
    -----------------------
    2008-05-22 10:36:35.447
    -----------------------
    2008-05-22 10:36:36.790
    -----------------------
    2008-05-22 10:36:44.480

    it seems so strange the to "select into" execution which much faster than "insert" ... is it correct or anything i missed out?

    platform: sql server 2005

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    i dont think you can conclude things so easily based on one or two executions . there are several other factors that effects the execution time of each sql on each run.....

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select into is faster because it is a minimally logged operation.
    It only logs the fact that pages was allocated but not the data

  4. #4
    Join Date
    Jul 2006
    Posts
    115
    Quote Originally Posted by pdreyer
    select into is faster because it is a minimally logged operation.
    It only logs the fact that pages was allocated but not the data
    oic, so direct copy table by "select into" can speed up processing & reduce trx log . but according to sql server manual, it's not recommend us to "select into" bcos it may lock tempdb system tables in long processing query

    any general guideline? e.g in term of output data size or processing time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •