CODEWITHSUNDEEP
sqloracle-databaseplsqlinsertbulkinsert
Mariya
Mariya

Reputation: 847

plsql insert multiple rows using one execute immediate command

Is there a way to insert multiple rows in one EXECUTE IMMEDIATE? Rather than writing EXECUTE IMMEDIATE for each insert...

Upvotes: 2

Views: 10481

Answers (5)

Md Jawed Shamshedi
Md Jawed Shamshedi

Reputation: 314

Sure, you can go for batch insert...

Upvotes: 0

Avi
Avi

Reputation: 1145

@maria First frame select quesry which gives u multiple row wich u are going to insert... You select stament shoud give data in order of data u want to insert in table

Then use..

Insert into Tabl1 (col1,col2,col3)(select name,address,phone from tabl2) commit;

Upvotes: 0

user123664
user123664

Reputation:

Mariya, why use dynamic sql in the first place? Most of the times scalability is not exactly improved using dynamic sql. The same is for readability. Debugging is harder .... In many cases there are also weird security issues.... I don't know why you use dynamic sql but if this is part of a production application I would reconsider using it.

Ronald.

Upvotes: 0

Julius Musseau
Julius Musseau

Reputation: 4155

EXECUTE IMMEDIATE
INSERT INTO table (col1, col2, col3) (
            SELECT 1 AS col1, 2 AS col2, 3 AS col3 FROM dual
  UNION ALL SELECT 4,         5,         6         FROM dual
  UNION ALL SELECT 7,         8,         9         FROM dual ) ;

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

Hard to tell what you are inserting. You can use EXECUTE IMMEDIATE to do an INSERT...SELECT easily enough, but I suspect that isn't what you are after, and probably you're not simply wanting a loop around the EXECUTE IMMEDIATE.

If the multi-table insert isn't what you are looking for, you can use EXECUTE IMMEDIATE on a PL/SQL block and/or within a FORALL

create table test_forall_dyn (val varchar2(1));

declare
  type tab_char is table of varchar2(1) index by binary_integer;
  t_char tab_char;
begin
  for i in 1..26 loop
    t_char(i) := chr(64 + i);
  end loop;
  forall i in 1..26
    execute immediate 
      'begin 
         insert into test_forall_dyn (val) values(:1);  
         insert into test_forall_dyn (val) values(:1); 
       end;' 
       using t_char(i);
end;
/

select count(*) from test_forall_dyn;

Upvotes: 8

Related Questions