SQL Server Performance

Insert Multiple Rows within a Single Round Trip

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Apr 18, 2007.

  1. PAMUR New Member

    Hi,

    Could we Insert Multiple Rows within a Single Round Trip?
    IF so can you guide me please.

    Thank you.


    Usha Rani
    PAMUR, Apr 18, 2007
    #2

  2. Roji. P. Thomas New Member

    Roji. P. Thomas, Apr 18, 2007
    #2

  3. FrankKalis Moderator

    Not sure I understand you. Do you mean something like INSERT INTO...SELECT...?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Apr 18, 2007
    #2

  4. PAMUR New Member

    ex: Insert Selected Categories from front end as multiple records into a detail table.

    I have constructed a string with IDS to insert, passed it to SP as a parameter, and then from SP I have used an

    Insert Into Table(guid, CatID)
    (Select @GUID, CAtID from ategories WHerre CatID in(IDS)

    to insert.

    IS there any other way than The above and XML?

    Thank you.


    Usha Rani
    PAMUR, Apr 18, 2007
    #2

  5. FrankKalis Moderator

    FrankKalis, Apr 18, 2007
    #2

  6. FrankKalis Moderator

    Hm?!?<br />I'm pretty much it IS mentioned. Even in the 2000 BOL. At least in the 2005 BOL you can find it at "Using INSERT with the VALUE Clause and a SELECT Subquery" and ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a2336a30-1793-4a29-bffc-500e24204d70.htm for example. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Apr 18, 2007
    #2

  7. PAMUR New Member

    But can't avoid dynamic SQL <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Usha Rani
    PAMUR, Apr 18, 2007
    #2

  8. FrankKalis Moderator

    Adriaan, what happened to your posting? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Apr 18, 2007
    #2

  9. Adriaan New Member

    Upload data to holding table (for instance in Access you can have ODBC linked tables, into which you can insert) then start stored procedure to do the merge?

    Upload a text/MDB/XLS file to SQL Server, then start DTS job or stored procedure to do the merge?
    Adriaan, Apr 19, 2007
    #2

  10. Adriaan New Member

    Frank, I deleted the posting when I noticed you guys had taken the discussion in another direction.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
    Adriaan, Apr 19, 2007
    #2

  11. PAMUR New Member

    ummm....
    Thank you.

    Usha Rani
    PAMUR, Apr 19, 2007
    #2

  12. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Hm?!?<br />I'm pretty much it IS mentioned. Even in the 2000 BOL. At least in the 2005 BOL you can find it at "Using INSERT with the VALUE Clause and a SELECT Subquery" and ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a2336a30-1793-4a29-bffc-500e24204d70.htm for example. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">And Frank - indeed the SELECT version is mentioned, but one of the problems is that there are multiple entries for INSERT INTO, and not all of them have the SELECT version.<br /><br />Now you've made me post what I deleted, you[xx(][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
    Adriaan, Apr 19, 2007
    #2

  13. FrankKalis Moderator

    Hey, cheap way to increase post count. [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Apr 19, 2007
    #2

  14. PAMUR New Member

    Both of you are nuts. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Usha Rani
    PAMUR, Apr 19, 2007
    #2

  15. Adriaan New Member

    Where are the bolts when you need them.
    Adriaan, Apr 19, 2007
    #2

  16. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Apr 19, 2007
    #2

  17. PAMUR New Member

    With me and SQL-Server [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Usha Rani
    PAMUR, Apr 19, 2007
    #2

Share This Page