INSERT INTO ... SELECT * FROM ...

This page answers questions like these:


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



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
Oracle Time in Microseconds
What Oracle Version do I Have?
Oracle Trigger Compilation Warnings/Errors
Defragment Tablespace

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


Tags: INSERT INTO SELECT Oracle, Oracle INSERT INTO SELECT, Oracle INSERT SELECT, INSERT INTO SELECT * FROM Oracle, INSERT SELECT Oracle, Oracle INSERT FROM SELECT, INSERT FROM SELECT Oracle, INSERT AS SELECT Oracle, SELECT * FROM INSERT INTO, SELECT * INTO Oracle, Oracle SELECT * INTO, SELECT * INSERT INTO table, SELECT * INSERT INTO, INSERT INTO SELECT * FROM, Oracle INSERT FROM SELECT, INSERT SELECT Oracle, INSERT INTO SELECT * FROM, 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

Copyright © HelpDoco.com
oracle-insert-select.txt
Oracle/insert-into-select-from.htm
6