INSERT INTO vs. SELECT for inputting values

  • Dear Greater Transact-SQL Intellect,

    My apologies for asking such a redimentary question...

    I was reading Jeff Moden's article: Cross Tabs and Pivots, Part 1 - http://www.sqlservercentral.com/articles/T-SQL/63681/

    Excellent article by the way.

    And I'm just wondering about the way he is inputting data into his #SomeTable1 table.

    The example shows the following:

    CREATE TABLE # SomeTable1

    (

    Year SMALLINT

    Quarter TINYINT

    Amount DECIMAL (2,1)

    )

    SELECT 2006, 1, 1.1 UNION ALL

    SELECT 2006 2, 1.2 UNION ALL

    etc.

    What the difference between using the SELECT statement to input data as opposed to:

    INSERT INTO #SomeTable1

    VALUES (2006, '1', '1.1')

    INSERT INTO #SomeTable1

    VALUES (2006, '2', 1.2')

    etc.

    ?

    Thanks.

    Regards,

    Mike G.

    Seattle, WA

  • The example using a set of SELECT statements linked by UNION ALLs is a single INSERT of the set of all rows. The other example executes a single INSERT for each individual row. There is much less overhead for the single INSERT of all rows, so it runs faster.

    P.S. The word is "rudimentary." 😉

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • I'd be curious to know if anyone has tested this. It's one transaction v many, and I would tend to agree with Bob, but on anything less than hundreds or thousands of rows, I wouldn't expect "faster" to be noticeable.

  • Steve Jones - Editor (10/5/2009)


    I'd be curious to know if anyone has tested this. It's one transaction v many, and I would tend to agree with Bob, but on anything less than hundreds or thousands of rows, I wouldn't expect "faster" to be noticeable.

    I seem to recall some while back that Jeff or someone had posted a test showing the UNION was faster.

    Either way, I like it because it really cuts down on visual clutter in the query plan.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I use it a lot in demonstration code for two reasons...

    1. It cuts down on the actual visual clutter and takes less horizontal and vertical space.

    2. I'm lazy. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mgodinez (10/5/2009)


    Excellent article by the way.

    Thanks for the kudo, Mike. I appreciate it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You're absolutely right, Steve. At that number of rows, hardly anything makes a noticeable difference.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Hello,

    Much obliged for the replies. I think I understand now.

    Agreeed, w/ such a small amount of inserted data...I didn't notice too much of a time difference, running either code. But thousands of lines, I would think the impact would be felt.

    Thanks again.

    Regards,

    Mike G.

    Seattle, WA

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

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