Inserting multiple rows in one table with just one insert commnad

A little hard to explain in SQL terms because I am using an in-house technology but Let's say I have an array of a structs (similar to structs we have in C#, C++, etc) and I want to insert its values in a table. So one way is a psedu-code that iterates through the array, read the fields of the structs and inserts them into the table like this:

for int i =1 to array.Lenght
{
    insert into MyTable values
    {
       MyTable.Field1 = array[i].Field1;
       //etc ...
    }
}

but this is bad, because of performnce. If array has ten elements we are calling insert method ten times. There should be a brillinat way of doing this with just one insert, somehow using JOINS on table and just call insert one time, But I can't imagine how to do that...

Any thoughts on this are welcome.

Thanks.

---

This makes sure the query is executed once. This is just an idea, avoiding multiple call insert

@sql = 'insert into mytable(col_1) values'
for int i =1 to array.Lenght
{
   if(i > 1)
    @sql = ',('+@sql + array[i].Field1+')'
   else
    @sql = '('+@sql + array[i].Field1+')'
}
@sql = @sql + ';'
exec @sql

Script looks like

 insert into mytable(col_1) values
 (1),(2),(3);

---

Insert multiple records into MySQL with a single query:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

http://www.electrictoolbox.com/mysql-insert-multiple-records/

链接地址: http://www.djcxy.com/p/94512.html

上一篇: INSERT INTO MySQL表错误

下一篇: 只需一个插入通讯插入一个表中的多行