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.
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
