Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Oct 2004
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts

    SQL Server - Inserting multiple rows in one query

    I am used to using MySQL but had to transfer to SQL server for work. I'm trying to figure out how to insert multiple rows with one query. In MySQL the query would be like this:

    Code:
    INSERT INTO Mytable (Name, Number) VALUES ('Joe', 18), ('Bob', 25), ('Mike', 7);
    I tried a query like the one above in SQL Server and it gave me an error that said: Incorrect syntax near ','.

    Is there a way to do this in SQL Server? Or will I have to resort to doing multiple insert statements?

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    What you can do is faking a subselect. In MSSQL, you can do a multiple insert by:
    Code:
    INSERT INTO thetable (field1, field2)
    SELECT field1, field2 FROM table2
    But this won't work for your situation, because it is new data. What you can do now is enter the data after the SELECT statement, as it came from another table. There you stray away from standard SQL, but in MSSQL it will work. It looks like this:
    Code:
    INSERT INTO thetable (field1, field2)
    SELECT value1, value2
    UNION ALL
    SELECT value3, value4
    UNION ALL
    .
    .
    .
    SELECT value5, value6;
    Last edited by Roelf; 03-15-2007 at 12:29 AM.
    I am the luckiest man in the world

  • #3
    New Coder
    Join Date
    Oct 2004
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for your reply. I haven't had a chance to test this out yet, but I'm sure it will work.

  • #4
    New to the CF scene
    Join Date
    Jun 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hmmm,,,here is a way to insert multiple values with a single querry in sql server2000

    It looks like this:
    Code:
    INSERT INTO thetable (field1, field2)
    SELECT 'value1', 'value2'
    UNION ALL
    SELECT 'value3', 'value4'
    UNION ALL
    .
    .
    .
    SELECT 'value5', 'value6'
    [/QUOTE]
    i m fully sure that it works bcoz i hav already try it.....


  •  

    Posting Permissions

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