Oracle insert statement Extension

The extension of the oracle insert statement sends a shotgun to a table: (1) insert into table_name [(column [, column...])] select_statement can only insert one table at a time, but the performance is higher than writing multiple insert statements. In addition, oracle also provides create table table_name as select..., as is indispensable! Directly create and insert tables, depending on the results of the select statement, such as: create table new_table as select * from source_table where 1 = 0 case: The table mxt adds a new column time, this column will be placed at the end. Now we want to place this column in 2nd create table copy_mxt as select id, time, name from mxt; drop table mxt; www.2cto.com rname copy_mxt to mxt; shotgun to multiple tables: Type: ① unconditional insert ② conditional insert all ③ conditional insert first ④ conditional insert multiple table insert restrictions: ① ONLY insert can be used for tables, but not for views or materialized views. ② this insert operation cannot be used for remote tables. ③ when multiple tables are inserted, you cannot specify a table set operation ④ the number of columns added to the into target table of Multi-table insert cannot exceed 999 (I) Unconditional insert Syntax: insert all into dest_table1 [column (, column...)] [values (...)] into dest_table2 [column (, column...)] [values (...)]... www.2cto.com
Select_statement -- the value column is the select result column. If no value column is specified, the column type and sequence of the target table are exactly the same as those of the select statement query. We recommend that you do not omit columns and values in the target table to improve readability. Example: [SQL] create table emp_1 as select employee_id, last_name from employees where 1 = 0; create table emp_2 as select * from employees where 1 = 0; create table emp_3 as select * from employees where 1 = 0; insert all
-- The columns after emp_1 are not specified, nor are the columns specified by values, in this case, all the column types and sequences of emp_1 are the same as those of the queried columns, that is, only the columns in the query results in emp_1, in addition, the type and sequence are consistent. into emp_1 -- specifies the columns after emp_2, but no values specifies the columns. This indicates that the columns to be inserted in emp_2 are selected, the type and sequence of the query result columns are the same. www.2cto.com -- emp_2 also has many columns, not only the two columns into emp_2 (employee_id, last_name) -- specify the columns after emp_3 and the specified columns of values, the column names after values must be consistent with the query results. If the query has an alias, the type and sequence of columns specified by the alias -- emp_3 must be consistent with that specified by values. -- emp_3 may also have more columns than the specified columns: into emp_3 (employee_id, last_name) values (e_id, e_last_name) select employee_id e_id, last_name e_last_name from employees (ii) insert with conditions includes: insert all and insert first Syntax: insert all when condition_statement then into dest_table1 [specify columns] values [columns in the query] when condition_statement then into dest_table2 [specify columns] values [columns in the query] select_statement
Note: The difference between insert all with conditions and insert first is that only query results are selected based on conditions, without considering the records that have already been matched. Full match is performed each time, insert first does not take into account records that have been matched. The next when will automatically filter out the previous matched row record www.2cto.com. Example: [SQL] insert all -- insert the query result s_id> 20. The column specified in the condition must be the same as the query result name. If there is an alias, please use the alias when e_id> 20 then into emp_1 -- s_last_name for inserts starting with M. The inserted rows may have duplicates with s_id. when e_last_name like'm % 'then into emp_2 (employee_id, last_name)
-- If else is specified, the insert to emp_3 does not meet the preceding conditions. The inserted row does not repeat the preceding two else into emp_3 (employee_id, last_name) values (e_id, e_last_name) select employee_id e_id, last_name e_last_name from employees (3) Rotating ting insert (rotating insert) three paradigms of relational database: ① rows cannot be divided into ② columns, and column 3 cannot be repeated. However, in many cases, the paradigm is destroyed. Rotating inserts can be used to convert non-relational table records to Relational Tables for database storage. This is similar to insert all, but it is used to send a shotgun to a table and change columns into rows.
Example: [SQL] create table sales (year_2008 number, year_2009 number, year_2010 number, year_2011 number, year_2012 number) insert into sales values (191,922,392,942,592) select * from sales organization create table cow_sales (year_sales number) insert all into cow_sales values (year_2008) into cow_sales values (region) into cow_sales values (year_2010) into cow_sales values) into cow_sales values (year_2012) select * from sales select * from cow_sales