Inserting multiple rows in one table with just one insert commnad

Question

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.

Also, you calling the commands from an OOP language, or are you looking for a pure SQL-script-based solution? — Arithmomaniac, Jul 03 '12 at 18:22
what about INSERT INTO SELECT - http://www.1keydata.com/sql/sqlinsert.html — feco, Jul 03 '12 at 18:24
"A little hard to explain in SQL terms because I am using an in-house technology" Are you really asking us to come up with a more efficient way of doing an insert into a proprietary in-house technology, about which we know nothing? Without knowing the technology, how on earth would we know about its strengths, weaknesses, and performance tips? And why tag this as SQL, when you specifically say it's not SQL? Without more details, this is ***not*** an answerable question. — David, Jul 03 '12 at 18:24
@DavidStratton : Think of it as PL-SQL or jsut SQL and see if you can solve it there. Same will apply to my language later. — Bohn, Jul 03 '12 at 18:26
@JamesHill : it is an in-house technology but it is very similar PL-SQL — Bohn, Jul 03 '12 at 18:27
OK. I'm not trying to be argumentative, but knowing the underlying technology is critical to tuning queries for performance. What works best in PL-SQL may or may not work best in DB2 SQL or T-SQL. It's not usually the SQL statement that matters, it's the DBMS and even the setup of the tables/files. For example, in SQL Server using .NET, you could use bulk upload to quickly upload an entire table where it's not an option with other DBs. I know that's not the task you're asking about, but it illustrates my confusion. But I'll hush up and let someone more constructive answer. — David, Jul 03 '12 at 18:30

score 5 · Answer 1 · answered Jul 03 '12 at 18:21

5

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/

answered Jul 03 '12 at 18:21

Alexey

7,127
9
57
94

1

How do you know that they are using mySQL? – James Hill Jul 03 '12 at 18:22
1

The same also applies for SQL Server 2008 - http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query – Duncan Howe Jul 03 '12 at 18:31

codingbiz · Accepted Answer · 2012-07-03T19:33:51.583

1

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);

edited Jul 03 '12 at 19:33

answered Jul 03 '12 at 18:47

codingbiz

26,179
8
59
96

Inserting multiple rows in one table with just one insert commnad

2 Answers2