Execute insert script from TOAD

Question

I used TOAD, Oracle 11g.

I want to create Batch Insert script.

Example

 INSERT INTO tbl_name (a,b,c) 
 VALUES(1,2,3),(4,5,6),(7,8,9);

But, when I use export dataset - insert statements, I get:

  Insert into tbl_name (1, 2, 3)
  Values (1, 2, 3);

 Insert into tbl_name (1, 2, 3)
 Values (1, 2, 3);

So, it takes a long time because there are too many rows.

Is there a way?

Thanks!

Oracle does not support multi-row insert statements. The only way you can do that is using multiple inserts. The syntax of your first statement does not work with Oracle. — , Nov 22 '16 at 07:30

score 1 · Answer 1 · answered Nov 22 '16 at 08:41

Unfortunately Oracle doesn't support to insert mutlitple rows with the values clause as in

INSERT INTO tbl_name (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9);

You'd have to convert this into an insert select for Oracle:

INSERT INTO tbl_name (a,b,c) 
  SELECT 1,2,3 FROM DUAL
  UNION ALL
  SELECT 4,5,6 FROM DUAL
  UNION ALL
  SELECT 7,8,9 FROM DUAL;

or write separate insert statements as you've already shown yourself.

score 0 · Answer 2 · answered Dec 05 '16 at 20:24

I have a daily audit where I have to update from a few rows to a couple of thousand. I surround the code with 'Begin / commit / end' and it executes in just a couple of seconds instead of the minutes it takes to run the updates one at a time.

begin 
update my_audit set audit_date = sysdate, audit_by = trim(q'{DBA for DLD}'), audit_comments = trim(q'{  RFC-009999 Fix blah blah blah for 1000 entries   }') , audit_complete = 'Y' where row_id = 4754973;
    ... 998 update statements here ...
 update my_audit set audit_date = sysdate, audit_by = trim(q'{DBA for DLD}'), audit_comments = trim(q'{     RFC-009999 Fix blah blah blah for 1000 entries   }') , audit_complete = 'Y' where row_id = 4755973;     
commit;
end;

I did run into a memory problem when I tried to do this with 50,000 rows. I just broke it up into multiple begin/commit/end blocks and re-ran, and everything worked fine.

Execute insert script from TOAD

2 Answers2