Differences between insert into select and select into in Oracle

In Oracle, copy the data of a table to another object. Usually there are two methods: insert into select and select into from.

The former can copy N rows (0 to any number) from the select statement to a new table, and the latter can only copy the "one row" result to a variable. In this case, select into is a value assignment statement of 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 the other as the target table.

1. Create TableT_source (
2. Id numberPrimary Key,
3. Testname varchar2 (20 ),
4. CreatetimeDate,
5. Flag varchar2 (10)
6. );
8. Create TableT_target (
9. Id numberPrimary Key,
10. Testname varchar2 (20 ),
11. CreatetimeDate,
12. Flag varchar2 (10)
13. );

Next, insert the test data.

1. Insert IntoT_sourceValues(1,'Test data 1... 1', Sysdate-2,'N');
2. Insert IntoT_sourceValues(2,'Test data 1... 2', Sysdate-2,'N');
3. Insert IntoT_sourceValues(3,'Test data 1... 3', Sysdate-2,'N');
4. Commit;

Test the insert into select operation.

1. Insert IntoTest2Select*FromT_sourceWhereId = 1;
2. Commit;

Test the select into operation.
Because select into is a copy statement in plsql, it is the same as: =.

1. Create Or Replace ProcedureSp_sync_testIs
2. Aa varchar2 (100 );
3. V_record t_source % rowtype;
4. Begin
5. SelectT1.testnameIntoAaFromT_source t1WhereId = 1;
6. Dbms_output.put_line ('Normal variable t1.testname ='| Aa );
8. SelectT1 .*IntoV_recordFromT_source t1WhereId = 1;
9. Dbms_output.put_line ('Record variable t1.testname ='| V_record.testname );
11. End;

The original type variables and record type variables are added for your understanding.