I have a 200 insert statement that can be inserted into a single table .How can i do this in sql developer to run that 200 insert statement at once.
1 Answer
There are various ways. I know of two for sure and there may be others:
Option 1)
with dta(id, Col1, Col2) as (
select 1, 'Some Value 1', date '2015-01-12' from dual union all
select 2, 'Some Value 2', date '2015-05-21' from dual union all
select 3, 'Some Value 3', date '2015-09-14' from dual
)
insert into your_table (id, col1, col2)
select id, col1, col2 from dta;
Option 2)
insert all
into your_table (id, col1, col2) values (1, 'Some Value 1', date '2015-01-12')
into your_table (id, col1, col2) values (2, 'Some Value 2', date '2015-05-21')
into your_table (id, col1, col2) values (3, 'Some Value 2', date '2015-09-14')
select * from dual;
I prefer option 1 since it's a bit more succinct than option 2 since you don't need to list out the columns every time. With option 2 it's easier to vary which columns get loaded since you can include or excluded different columns for each into statement. This is only of benefit when there are potentially large numbers of null value columns or columns that should take default values.
