Tuesday, October 02, 2012

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>

No comments: