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);