Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2011
    Posts
    5

    Unanswered: Different ways to insert multiple rows at one go.

    Hi,
    I want to insert multiple rows at one go instead of writing insert into statement multiple times.So can any one please tell me different ways to do this.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are a number of ways to do multiple row inserts, although theere are about a dozen that will suffice for most purposes. If you can give a specific example of what you want to insert and where that needs to be inserted then I can tell you how I would do it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    bcp 4567890

  4. #4
    Join Date
    Feb 2011
    Posts
    5

    Post

    [QUOTE=Pat Phelan;6495015]There are a number of ways to do multiple row inserts, although theere are about a dozen that will suffice for most purposes. If you can give a specific example of what you want to insert and where that needs to be inserted then I can tell you how I would do it.]

    Hi Pat ,
    I have one form through which user is entering number of products in application but in as single table.So currently if user is entering 5 products then I am firing insert query 5 times,if 6 products then firing insert query for 6 times & hence forth.....
    So can you please tell me various ways to insert multiple rows in a table at one go using sql server.

    Thanks,
    Amit.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I want to insert multiple rows at one go
    Is this what you were looking for?
    Code:
    INSERT INTO DaTable (col1, Col2) VALUES
    (val1, val2), 
    (val3, val4),
    (val5, val6), 
    ...
    You didn't specify in what form those multiple rows exist. When they reside in a CSV-file, you may wish to use BCP.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2011
    Posts
    5
    This solution is fine if I know exact row number to insert. But in my case every time I have to insert variable number of rows.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    How do you know what rows you have to insert? How do they reach you? On paper? In a CSV-file? In an Excel file? ...?

    Without this information we can not help you.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Feb 2011
    Posts
    5
    Our application is having 20 text boxes on a product entry page.So it depends on user,he can enter values in 5 ,7,8 or etc text boxes & hit save button.So by collecting these much rows at runtime I want to insert into database at one go.

    Thanks.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Pseudo code:
    Code:
    def Save(HTMLPage):
    for i in range (1, 20):
      if HTMLPage.textBox[i] is not None:
        INSERT INTO DaTable (col1) VALUES HTMLPage.textBox[i].value
    Add some transaction logic and you're done.

    This is what you need, a one click solution that dynamically stores 0 to 20 values from your text boxes. Though I think you already came up with this solution a long time ago. You want something without a loop, right?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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