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:
- When inserting into one table directly from another without naming the columns:
- The number of columns in both tables must be the same.
- The values in the source columns must be compatible with the types of the destination columns.
- The column names are irrelevant and are ignored!
- The column order is important!
- The Nth column of the source table will be put into the Nth column of the destination table.
- If the types or values are incompatible, an error will occur.
- N.B. These rules apply no matter whether the INSERT is done using SQL*Plus or PL/SQL or Pro*C.
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