Simple INSERT Statements

This was tested on Oracle 11.2. Before I could can do any INSERT statements, I needed to create a table:

SQL> create table source

  2  (col1 varchar2(1),

  3   col2 varchar2(1))

  4  /

Table created. 

SQL>

Then I tried various single-row INSERT statements. Oracle converted the NUMBER columns (i.e. the ones without quotes) into VARCHAR2 format with or without the TO_CHAR function:

SQL> insert into source

  2  (col1,col2) values(to_char(1),'2')

  3  /

1 row created.

SQL> insert into source values('3',4)

  2  /

1 row created.

SQL> insert into source (col1,col2)

  2  select 5,'6' from dual

  3  /

1 row created.

SQL> insert into source

  2  select '7',to_char(8) from dual

  3  /

1 row created.

SQL> select * from source

  2  /

C C

- -

1 2

3 4

5 6

7 8 

SQL> 

Next I created an empty copy of the table:

SQL> create table target

  2  as select * from source

  3  where 1=2

  4  /

Table created. 

SQL> 

... and copied the rows from SOURCE to TARGET with 2 different multi-row INSERT statements:

SQL> insert into target select * from source

  2  /

4 rows created.

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

4 rows created. 

SQL> 

I dropped a column from the SOURCE table:

SQL> alter table source drop column col2

  2  /

Table altered. 

SQL> 

... and both the INSERT statements then failed albeit with different error messages:

SQL> insert into target select * from source

  2  /

insert into target select * from source

            *

ERROR at line 1:

ORA-00947: not enough values

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

select col1,col2 from source

            *

ERROR at line 2:

ORA-00904: "COL2": invalid identifier

SQL>

I replaced the dropped column and added an extra one. In my experience, adding columns is far more common than dropping columns:

SQL> alter table source add

  2  (col2 varchar2(1),

  3   col3 varchar2(1))

  4  /

Table altered.

SQL>

Then only the first INSERT failed:

SQL> insert into target select * from source

  2  /

insert into target select * from source

            *

ERROR at line 1:

ORA-00913: too many values

SQL> insert into target(col1,col2)

  2  select col1,col2 from source

  3  /

4 rows created. 

SQL>