INSERT INTO ... SELECT * FROM ...

This page answers questions like these:

Related Links:
Oracle Date and/or Time in Seconds
Defragment Tablespace
What Oracle Version do I Have?
Oracle Trigger Compilation Warnings/Errors



Inserting into a Table that has Identical Column Names and Types:

-- Two-column table, with a NUMBER and a VARCHAR2. CREATE TABLE table1 ( num NUMBER(2), alpha VARCHAR2(2) ); -- Identical column names and types. CREATE TABLE table2 ( num NUMBER(2), alpha VARCHAR2(2) ); -- Insert some values. INSERT INTO table1 VALUES (12, 'XY'); -- Copy rows from table1 to table2. SQL> INSERT INTO table2 SELECT * FROM table1; 1 row created. -- It succeeds as expected.

Inserting into a Table that has Different Column Names:

-- Two-column table, with a NUMBER and a VARCHAR2. CREATE TABLE table1 ( num NUMBER(2), alpha VARCHAR2(2) ); -- Different column names, Same types. CREATE TABLE table5 ( n NUMBER(2), a VARCHAR2(2) ); -- Insert some values. INSERT INTO table1 VALUES (12, 'XY'); -- Copy rows from table1 to table5. SQL> INSERT INTO table5 SELECT * FROM table1; 1 row created. -- It succeeds because the column names are irrelevant.

Inserting into a Table that has Bigger Types:

-- Two-column table, with a NUMBER and a VARCHAR2. CREATE TABLE table1 ( num NUMBER(2), alpha VARCHAR2(2) ); -- Same column names, Bigger types. CREATE TABLE table3 ( num NUMBER(20), alpha VARCHAR2(20) ); -- Insert some values. INSERT INTO table1 VALUES (12, 'XY'); -- Copy rows from table1 to table3. SQL> INSERT INTO table3 SELECT * FROM table1; 1 row created. -- It succeeds because the source column values and the destination column types are compatible.

Inserting into a Table that has Smaller Types:

-- Two-column table, with a NUMBER and a VARCHAR2. CREATE TABLE table1 ( num NUMBER(2), alpha VARCHAR2(2) ); -- Same column names, Smaller types. CREATE TABLE table4 ( num NUMBER(1), alpha VARCHAR2(1) ); -- Insert values which fill their columns. INSERT INTO table1 VALUES (12, 'XY'); -- Try to copy rows from table1 to table4. SQL> INSERT INTO table4 SELECT * FROM table1; INSERT INTO table4 SELECT * FROM table1 * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column -- It fails because the source column values and the destination column types are incompatible. -- i.e. You’re trying to copy the value '12' from column "table1.num" into "table4.num", but it won’t fit. -- N.B. If you were inserting NULL values or values that were short enough to fit, it would be OK!

Inserting into a Table that has the Columns out of Order:

-- Two-column table, with a NUMBER and a VARCHAR2. CREATE TABLE table1 ( num NUMBER(2), alpha VARCHAR2(2) ); -- Same column names and types, but column order is different. CREATE TABLE table6 ( alpha VARCHAR2(2), num NUMBER(2) ); -- Insert some values. INSERT INTO table1 VALUES (12, 'XY'); -- Try to copy rows from table1 to table6. SQL> INSERT INTO table6 SELECT * FROM table1; INSERT INTO table6 SELECT * FROM table1 * ERROR at line 1: ORA-01722: invalid number -- It fails because the source column values and the destination column types are incompatible. -- You can’t do an insert using “SELECT *” if the tables have a different column ordering. -- i.e. You’re trying to copy the VARCHAR2 value 'XY' from column "table1.alpha" into column "table6.num" which is of type NUMBER. -- N.B. If you were inserting NULL values, it would be OK!

INSERT INTO table SELECT * FROM table — Rules Summary:





Related Links:
Oracle Date and/or Time in Seconds
Defragment Tablespace
What Oracle Version do I Have?
Oracle Trigger Compilation Warnings/Errors

Home  >  Oracle  >  INSERT INTO ... SELECT * FROM ...


Tags: insert into select, insert ... select ..., insert into table select * from table, column order, different column ordering, different column order, does column order matter, is column order important, select * from, ORA-01438: value larger than specified precision allowed for this column, ORA-01722: invalid number, oracle database, oracle, PL/SQL, PLSQL, Pro*C, ProC, database

oracle-insert-select.txt
Oracle/insert-into-select-from.htm
7