Alex Alex - 4 days ago 8
SQL Question

Insert multiple rows

I've seen in many places a good approach to insert several rows in Oracle is as follows:

INSERT INTO mytable ("id", "name") VALUES ('val1', 'val2');
INSERT INTO mytable ("id", "name") VALUES ('aa', 'cc');
INSERT INTO mytable ("id", "name") VALUES ('ww', 'dd');


But, in the case of a bulk insert of many rows, is that supposed to be good-performant, given that we're executing separated queries? What about the classic overhead problem in these cases of multi-querying (networking, parsing,...)?

Thank you in advance!!

Answer

That is indeed an inefficient way to insert into a table. The exact statements you illustrated could be replaced with

INSERT INTO mytable ("id", "name") 
  select 'val1', 'val2' from dual union all
  select 'aa'  , 'cc'   from dual union all
  select 'ww'  , 'dd'   from dual
;

Of course, if your table has more than two columns, the other columns will be null (or the default value for the column, if so defined).

Even better, the values you are inserting are already in a table of some sort - then you can use a single SELECT statement, perhaps with WHERE conditions, joins, etc.

Comments