PL/SQL – insert, truncate, merge

Insert examples

insert into hr.regions values (7, 'Space'); -- insert single row using columns positioning
insert into hr.regions (region_id, region_name) values (8, 'Milky Way'); -- insert single row explicitly selecting columns
insert into hr.regions2 select * from regions; -- insert all rows from one table to another

There exists command to insert rows to many tables at once, search for ‘INSERT ALL‘.
Insert command creates rows in a table, but there are far more efficient ways to insert data: SQLLoader, DataPump.

To remove all rows from table delete and truncate commands can be used. Be carefull, truncate is extremely fast, but cannot be rolled back. It is not DML statement, it clears ‘high water’ mark of the table.

Merge command is used for example when you have two tables and you want to update one table using data from the second one. Merge can insert, update and delete rows in the same time.

merge into employees e using new_employees n
on (e.employee_id = n.employee_id)
when matched then
update set e.salary=n.salary
when not matched then
insert (employee_id,last_name,salary)
values (n.employee_id,n.last_name,n.salary);

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s