SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple inserts with MS SQL

    hello,
    I have a phone book entry for users on my website, and wondering how i could do a multiple insert into the database.

    PROBLEM:
    If a user wants to create multiple entries for his phone book and enters up to 10 names. HOw do i insert all these names into my db at the same time. Since each phone number is unique and would be in a new row.

    I know i could use a for each next in my ASP Page, but that would mean opening my db up to 10times. How do i do so in MS SQL ?

    thanks
    Afrika

  2. #2
    SitePoint Guru puco's Avatar
    Join Date
    Feb 2005
    Location
    Slovakia
    Posts
    785
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can concatenate the INSERTs into one batch and send it to the server:

    INSERT INTO PhoneBook VALUES ('name1', 'number1');INSERT INTO PhoneBook VALUES ('name2', 'number2');INSERT INTO PhoneBook VALUES ('name3', 'number3');...
    Martin Pernecky

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very funny,
    I was working on concatenations just on friday and NEVER Thought of it this way.

    Thanks Puco
    Afrika

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    since in SQL Server you don't actually have to have a FROM clause, you can do everything in one statement like this--
    Code:
    insert 
      into PhoneBook 
    select 'name1', 'number1'
    union all
    select 'name2', 'number2'
    union all
    select 'name3', 'number3'
    ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh ok,
    thanks Rudy


Bookmarks

Posting Permissions

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