Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2006
    Posts
    115

    Unanswered: insert with union all vs multi select

    if there are 2 insert statement

    insert ... select * from table1 union all select * from table2
    or
    insert ... select * from table1
    insert ... select * from table2

    pls advise which 1 is faster ...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * is a bad idea

    In any case, with 1 statement, you are only making 1 trip to the db
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with two statements, properly separated by a semicolon, you are also making only one trip to the database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    the proper way of doing it (without knowing what the process is for) is this:

    begin tran
    insert ... select * from table1
    if @@error !=0 or @@rowcount = 0 begin <error_handler> end
    insert ... select * from table2
    if @@error !=0 or @@rowcount = 0 begin <error_handler> end
    commit tran
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I guess this one depends on what you count as a "trip to the server" because you can see it two ways...

    Rudy is correct in that one SQL batch passes from the client to the transact-SQL front end interpreter either way.

    Blindman is correct in that one INSERT statment with two SELECT statements makes a single pass from the transact-SQL interpreter to the database engine.

    The final answer depends on what you measure.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    rdjabarov: Why do you consider zero rows to be an error?

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    ... a single pass from the transact-SQL interpreter to the database engine.
    yeah, but they're right next door to each other

    actually they're in bed with each other

    it's not like the pass from the front-end client to the transact-SQL interpreter

    that's like a trip to the next town in comparison
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    rdjabarov: Why do you consider zero rows to be an error?

    -PatP
    I don't always consider it an error, but it may be viewed as a logical error in certain situations. This scenario is very common in multi-user env's, as well as in ETL cases.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    True, but at least in my environment network time is single digit ms, parse/prepare is double digit ms, execution is three or more digits. Why squable over the details?

    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry Brett, I meant you but typed blindman instead.

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm the good looking one. Please do not get us confused again.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by Pat Phelan
    True, but at least in my environment network time is single digit ms, parse/prepare is double digit ms, execution is three or more digits. Why squable over the details?
    If it were just two inserts, the squable wouldn't be worth the time it took to sigh. But, two inserts will take longer than one insert, whether you count the round trips or not. If you had thousands of inserts, this would certainly be a measurable difference.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    I'm the good looking one. Please do not get us confused again.
    Sorry, neither of you looks good enough to make any difference to me... Then again, I rarely notice how people look as long as they make an effort to make themselves at least moderately presentable.

    The only person whose looks excite me happens to be a lady that shares an address with me, so you fellas are just plain out of luck!

    -PatP

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    all of the nice boys are taken. Personally I am going with the INSERT with the SELECTs in a union.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Jul 2006
    Posts
    115
    thx all of the nice boys...

    when "union" is used, will sql server execute all select sqls under union concurrently?

Posting Permissions

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