In Oracle, the into statement can copy the specified row result set from the select to a new table. The syntax is "Insert into Table2(field1,field2...) select value1,value2,.. .from Table1"; you can also copy the result of a row into a variable, the syntax is "SELECT vale1, value2 into Table2 from Table1".
The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.
There are usually two methods: insert into select and select into from.
The former can copy the selected N rows (0 to any number) result set into a new table, while the latter can only copy the "one row" result into a variable. Let's put it this way, select into is an assignment statement of PL/SQL language. The former is a standard SQL statement.
1.INSERT INTO SELECT statement
The statement form is: Insert into Table2(field1,field2,...) select value1,value2,... from Table1
Note:
(1) The target table Table2 must exist, and the fields field, field2... must also exist
(2) Pay attention to the primary key constraints of Table2, If Table2 has a primary key and is not empty, then field1, field2... must include the primary key
(3) Pay attention to the syntax, do not add values, and it will be confused with the sql for inserting a piece of data. Do not write it as:
Insert into Table2(field1,field2,...) values (select value1,value2,... from Table1)
Since the target table Table2 already exists, we except inserting the source table Table1 In addition to fields, constants can also be inserted.
2.SELECT INTO FROM statement
The statement form is: SELECT vale1, value2 into Table2 from Table1
The required target table Table2 does not exist because Table Table2 will be automatically created during insertion, and the specified field data in Table1 will be copied to Table2.
Recommended tutorial: "Oracle Video Tutorial"
The above is the detailed content of How to use the into statement in oracle. For more information, please follow other related articles on the PHP Chinese website!