Oracle SELECT INTO and INSERT into SELECT Two table copy statements in detail

In Oracle, select intoFrom can not be used, with createTableSelect to replace this function!!! It works correctly in SQL Server. 1.INSERT  intoThe SELECT statement statement is in the form:Insert  intoTable2 (Field1,field2,...)SelectValue1,value2,... fromTable1 Note: (1requires that the target table Table2 must exist, and the field field,field2 ... must also exist (2Note Table2 's primary KEY constraint, if Table2 has a primary key and is not empty, then field1, field2 ... Must include the primary key (3Note the syntax, do not add values, and insert a data SQL mixed, do not write:Insert  intoTable2 (Field1,field2,...)Values(SelectValue1,value2,... fromTable1) because the target table Table2 already exists, we can insert constants in addition to the fields Table1 the source table. Examples are as follows:--1. Create a test table    Create TABLETable1 (Avarchar(Ten), Bvarchar(Ten), Cvarchar(Ten)    )    Create TABLETable2 (Avarchar(Ten), Cvarchar(Ten), Dint    )    --2. Create test Data    Insert  intoTable1Values('Zhao','ASDs',' -')      Insert  intoTable1Values('Money','ASDs',' -')      Insert  intoTable1Values('Sun','ASDs',' the')      Insert  intoTable1Values('Li','ASDs',NULL)      Select *  fromTable2--3.INSERT into SELECT statement to replicate table data    Insert  intoTable2 (A, C, D)SelectA,c,5  fromTable1--4. Display the updated results    Select *  fromTable2--5. Deleting a test table    Drop TABLETable1Drop TABLETable2 Example:-Using the Insert into SelectCopy data (note the Red section, you can automatically generate ID sequence values)Insert  intotest2 (ID,TESTNAME,CREATETIME,FALG)SelectSeq_test.nextval,t1.testname,t1.createtime,t1.falg fromtest1 T1;2.SELECT  intothe FROM statement form is:SELECTVale1, value2 intoTable2 fromTable1 requires that the target table Table2 not exist because the table Table2 is automatically created on insert and the specified field data in Table1 is copied to Table2. Examples are as follows:ViewPlaincopy toclipboardprint? --1. Create a test table    Create TABLETable1 (Avarchar(Ten), Bvarchar(Ten), Cvarchar(Ten)    )    --2. Create test Data    Insert  intoTable1Values('Zhao','ASDs',' -')      Insert  intoTable1Values('Money','ASDs',' -')      Insert  intoTable1Values('Sun','ASDs',' the')      Insert  intoTable1Values('Li','ASDs',NULL)      --3.SELECT to from statement CREATE table Table2 and copy data    SelectA,c intoTable2 fromTable1--4. Display the updated results    Select *  fromTable2--5. Deleting a test table    Drop TABLETable1Drop TABLETable2 Note: If the SQL/Plus or PL/SQL executes this statement and will report "ORA-00905: Missing keyword "error because pl/SQL and T-the difference between SQL. T-The sentence in SQL is normal, but PL/The explanation in SQL is:Select.. into  isPart ofPl/SQL language which means you have to  UseIt inside a PL/SQL block. You can not  UseItinchA SQL statement outside ofPl/SQL. That cannot be performed as a single SQL statement, typically in PL/used in the SQL program block (block). If you want the PL/This functionality is implemented in SQL, you can use the CreateTableNewTable as Select *  from ... : For example:Create TableNewTable as Select *  fromATable; NewTable except for the keys, the others are the same as atable.---------SQL SELECT into syntax introductionSqlSELECT  intostatement can be used to create a backup copy of a table. SELECT  intoStatementSELECT  intostatement to select data from one table and then insert the data into another table. SELECT  intostatements are often used to create a backup copy of a table or to archive records. SQLSELECT  intosyntax You can insert all columns into a new table:SELECT *  intoNew_table_name[In externaldatabase]  fromOld_tablename or just insert the desired column into the new table:SELECTCOLUMN_NAME (s) intoNew_table_name[In externaldatabase]  fromOld_tablenamesqlSELECT  intoInstance-make backup Copy the following example makes a backup copy of the "Persons" table:SELECT *  intoPersons_backup fromPersonsinchclause can be used to copy a table to another database:SELECT *  intoPersonsinch 'Backup.mdb'  frompersons if we want to copy certain fields, we canSELECTThese fields are listed after the statement:SELECTLastname,firstname intoPersons_backup fromPersonssqlSELECT  intoInstance-WithWHEREclause We can also addWHEREclause. The following example creates a table named "Persons_backup" with two columns by extracting information from the "Persons" table of people residing in "Beijing":SELECTLastname,firstname intoPersons_backup fromPersonsWHERECity='Beijing'SQLSELECT  intoInstance-It is also possible to select data from more than one table in a connected table. The following example creates a new table named "Persons_order_backup" that contains the information obtained from the Persons and Orders two tables:SELECTPersons.lastname,orders.orderno intoPersons_order_backup fromPersonsINNER JOINOrders onPersons.id_p=Orders.id_p

Source: 78560973

Oracle SELECT INTO and INSERT into SELECT Two table copy statements in detail