insert.out

derby database source code.good for you.

ij> --
-- this test is for basic insert functionality
--
-- NOTE: drop, create, select from the same table doesn't work yet either.
-- create the tables
create table t1 (i int, j int);
0 rows inserted/updated/deleted
ij> create table t2 (k int, l int);
0 rows inserted/updated/deleted
ij> -- populate t2
insert into t2 values (1, 2);
1 row inserted/updated/deleted
ij> insert into t2 values (3, 4);
1 row inserted/updated/deleted
ij> -- select * from t2
insert into t1 select * from t2;
2 rows inserted/updated/deleted
ij> insert into t1 (i, j) select * from t2;
2 rows inserted/updated/deleted
ij> insert into t1 (j, i) select * from t2;
2 rows inserted/updated/deleted
ij> select * from t1;
I          |J          
-----------------------
1          |2          
3          |4          
1          |2          
3          |4          
2          |1          
4          |3          
ij> -- drop and recreate t1
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1 (i int, j int);
0 rows inserted/updated/deleted
ij> -- select column list from t2
insert into t1 select k, l from t2;
2 rows inserted/updated/deleted
ij> insert into t1 select l, k from t2;
2 rows inserted/updated/deleted
ij> insert into t1 (i, j) select k, l from t2;
2 rows inserted/updated/deleted
ij> insert into t1 (j, i) select k, l from t2;
2 rows inserted/updated/deleted
ij> select * from t1;
I          |J          
-----------------------
1          |2          
3          |4          
2          |1          
4          |3          
1          |2          
3          |4          
2          |1          
4          |3          
ij> -- drop and recreate t1
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1 (i int, j int);
0 rows inserted/updated/deleted
ij> -- select constants from t2
insert into t1 select 5, 6 from t2;
2 rows inserted/updated/deleted
ij> insert into t1 (i, j) select 5, 6 from t2;
2 rows inserted/updated/deleted
ij> insert into t1 (j, i) select 6, 5 from t2;
2 rows inserted/updated/deleted
ij> select * from t1;
I          |J          
-----------------------
5          |6          
5          |6          
5          |6          
5          |6          
5          |6          
5          |6          
ij> -- drop and recreate t1
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1 (i int, j int);
0 rows inserted/updated/deleted
ij> insert into t1 (i) select 666 from t2;
2 rows inserted/updated/deleted
ij> insert into t1 (j) select 666 from t2;
2 rows inserted/updated/deleted
ij> select * from t1 where i = 666 or j = 666;
I          |J          
-----------------------
666        |NULL       
666        |NULL       
NULL       |666        
NULL       |666        
ij> -- drop and recreate t1
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1 (i int, j int);
0 rows inserted/updated/deleted
ij> -- Negative test cases - column references in values clause
insert into t1 values(1, c1);
ERROR 42X04: Column 'C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C1' is not a column in the target table.
ij> insert into t1 values("asdf asdf", 2);
ERROR 42X04: Column 'asdf asdf' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'asdf asdf' is not a column in the target table.
ij> -- Negative test case - syntax error