Oracle in SELECT into and insert into ... Select Difference

In Oracle, data from one table is copied to another object. There are usually two ways of doing this: INSERT into select and select into from.

The former can copy a select of n rows (0 to any number) result set into a new table, which can only copy the "one row" result into a variable. Let's say, select INTO is an assignment statement for PL/SQL language. The former is a standard SQL statement.

By doing a simple test, we can easily see the difference between the two.

First, we create two tables, one as the source table and one as the target table.

Create TableT_source (ID Number Primary Key, TestNamevarchar2( -), Createtime date, flagvarchar2(Ten)   ); Create TableT_target (ID Number Primary Key, TestNamevarchar2( -), Createtime date, flagvarchar2(Ten)   );  

Next, insert the test data

Insert  intoT_sourceValues(1,'test Data 1 .... 1', Sysdate-2,'N'); Insert  intoT_sourceValues(2,'test Data 1 .... 2', Sysdate-2,'N'); Insert  intoT_sourceValues(3,'test Data 1 .... 3', Sysdate-2,'N'); Commit; 

Test INSERT INTO Select operation

Insert  into Select *  from where id=1;    Commit

Test the SELECT INTO operation
Because select INTO is a copy statement in a Plsql language, the same as: = The target of the implementation.

Create or Replace procedureSp_sync_test isAAvarchar2( -); V_record T_source%RowType; begin    SelectT1.testname intoAa fromT_source T1whereId= 1; Dbms_output.put_line ('Common variable t1.testname=' ||AA); SelectT1.*  intoV_record fromT_source T1whereId= 1; Dbms_output.put_line ('record variable t1.testname=' ||v_record.testname); End; 

The variables of the primitive type and the record type are added here for easy understanding.

Oracle in SELECT into and insert into ... Select Difference