Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Unanswered: INSERT multiple values with one query

    I want to know if MS SQL has ability to INSERT multiple values with one statement. For example MySQL is able to do it like so

    INSERT INTO myTable (col1,col2,col3)
    VALUES
    ('1','1','1'),
    ('2','2','2'),
    ('3','3','3'),
    ('4','4','4');

    and this will insert four rows.

    Does MS SQL Have anything similar or do I have to repeat the INSERT statement if I need to generate a dynamic insert?

    Thanks,
    Lito

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Here's one way to do it with SQL Server:


    create table #temp (col1 char(1), col2 char(1), col3 char(1))

    INSERT INTO #temp (col1,col2,col3)
    select '1','1','1'
    UNION
    select '2','2','2'
    UNION
    select '3','3','3'
    UNION
    select '4','4','4'

    select * from #temp

    drop table #temp

  3. #3
    Join Date
    Nov 2003
    Posts
    46
    Thanks tomh53,
    Although this is a different way of doing this, it won't make it any easier for me. I don't have a problem of adding the insert statement to each line, I just wanted to write a more efficent query.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Tom's query is significantly more efficient than doing four separate INSERT operations, if that is what you're looking for....

    MS-SQL only supports the SQL-92 standard and a few Sybase/Microsoft extensions. At least as far as I know, it doesn't have any "MySQL compatability mode" if that is what you are trying to find.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    creating a temp table and inserting a UNION select is "significantly" more efficient than doing four inserts? and how are the contents of the temp table supposed to get inserted into the myTable table? that part seems to have been conveniently overlooked
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    creating a temp table and inserting a UNION select is "significantly" more efficient than doing four inserts? and how are the contents of the temp table supposed to get inserted into the myTable table? that part seems to have been conveniently overlooked
    I think that Tom's temp table was the table receiving the rows, he was just being tidy and not cluttering his name space for a simple demo. If so, then the rows ended up where he intended and no additional movement would be needed.

    Yes, if you take a look at the plan generated by four separate INSERT operations versus a single INSERT of a union of four SELECT statments, Tom's query looks more efficient to me and it definitely runs faster on my machine.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, fine, but a newbie is gonna see that temp table and assume a temp table is required for the solution

    this is why it is always so important to show exactly the syntax required, and nothing more

    so what if you feel like testing it on your own temp table, go the extra step and translate it into terms that are relevant to the original poster

    by the way, that "mysql" syntax is in the sql standard now, let's see how many years it takes for sql server to catch up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2003
    Posts
    46
    Thanks for going in depth for me...
    Just so you know this dynamic query is being built in a c# application, and when I meant more efficient I was talking about lines of code to generate that sql, not necessarily so it runs more efficient which is always a plus. I am dealing with less the 100 rows inserted right now and I'm not expecting to see a single insert with a 1000 rows for years to come. This is going to be a scheduled application and no one will be timing it or waiting for it to complete.

    Still it is good to know that UNION works faster then a separate Insert statement.

    I don't know if anyone will be able to confirm this, but I was reading a blog where someone suggested that using transactions helps reduce the log size dramatically with this kind of inserts

    -Lito
    Last edited by lito; 10-26-04 at 20:26.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    by the way, that "mysql" syntax is in the sql standard now, let's see how many years it takes for sql server to catch up
    Huh? Which "standard" would that be? I've only seen that syntax in MySQL and Mimer.

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Row and table constructors in SQL 2003

    Edit: in SQL 99, non-core feature, also implemented in DB2
    Last edited by r937; 10-27-04 at 10:22.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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