Oracle Insert Usage Summary

Summarize the Insert usage in Oracle

1. Standard Insert-single row insertSyntax: INSERT into table [(Column1,column2,...)]   VALUE (value1,value2,...)    Example: INSERT into DEP (Dep_id,dep_name) VALUES (1, ' technical Department '); Note: Only one piece of data can be inserted using standard syntax, and data can only be inserted in one table 2, unconditional insert all-multi-table multi-row InsertGrammar:INSERT [All] [Condition_insert_clause][Insert_into_clause Values_clause] (subquery)Example:INSERT AllInto sal_history (emp_id,hire_date,salary) VALUES (empid,hiredate,sal)Into mgr_history (emp_id,manager_id,salary) VALUES (empid,hiredate,sal)SELECT employee_id empid,hire_date hiredate,salary sal,manager_id MgrFrom employeesWHERE employee_id>200; 3, conditional insertGrammar:INSERT [All | First]When condition then Insert_into_clause values_clause[when condition then] [insert_into_clause Values_clause]......[ELSE] [insert_into_clause Values_clause]subquery;Example:Insert AllWhen id>5 and into Z_test1 (ID, name) VALUES (id,name)When Id<>2 and into z_test2 (ID) VALUES (ID )else into Z_TEST3 values (name)select Id,name from Z_test;When using the ALL keyword, Oracle judges each condition from top to bottom, and executes the following into statement when the condition is metIn The example above, if Id=6 then inserts a record in the Z_test1, and also inserts a record in the Z_test2Note: When using the first keyword, Oracle will judge each condition from top to bottom, execute the following into statement when encountering the initial satisfaction, and interrupt the judging criteria, in the example above, if id=6, only inserts a single piece of data into the z_test1. 4, rotate Insert (pivoting insert)CREATE TABLE Sales_source_data (employee_id Number (6),week_id Number (2),Sales_mon Number (8,2),sales_tue Number (8,2),sales_wed Number (8,2),Sales_thur Number (8,2),Sales_fri Number (8,2)); INSERT into sales_source_data values (176,6,2000,3000,4000,5000,6000); CREATE TABLE Sales_info (employee_id Number (6),Week number (2),Sales Number (8,2)); Looking at the table structure above, it is now time to convert the data in the Sales_source_data table into the Sales_info table, which requires the use of the rotate insert example as follows:Insert Allinto sales_info values (Employee_id,week_id,sales_mon)into sales_info values (employee_id,week_id,sales_tue)into sales_info values (employee_id,week_id,sales_wed)into sales_info values (Employee_id,week_id,sales_thur)into sales_info values (EMPLOYEE_ID,WEEK_ID,SALES_FRI)Select Employee_id,week_id,sales_mon,sales_tue,Sales_wed,sales_thur,sales_friFrom Sales_source_data;As can be seen from this example, the so-called rotary Insert is an unconditional insert all of a special application, but this application by the Oracle official, gave a pivoting insert name, that is, rotary insert

Oracle Insert Usage Summary