insert.sql

derby database source code.good for you.

--
-- 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);
create table t2 (k int, l int);

-- populate t2
insert into t2 values (1, 2);
insert into t2 values (3, 4);

-- select * from t2
insert into t1 select * from t2;
insert into t1 (i, j) select * from t2;
insert into t1 (j, i) select * from t2;
select * from t1;

-- drop and recreate t1
drop table t1;
create table t1 (i int, j int);

-- select column list from t2
insert into t1 select k, l from t2;
insert into t1 select l, k from t2;
insert into t1 (i, j) select k, l from t2;
insert into t1 (j, i) select k, l from t2;
select * from t1;

-- drop and recreate t1
drop table t1;
create table t1 (i int, j int);

-- select constants from t2
insert into t1 select 5, 6 from t2;
insert into t1 (i, j) select 5, 6 from t2;
insert into t1 (j, i) select 6, 5 from t2;
select * from t1;

-- drop and recreate t1
drop table t1;
create table t1 (i int, j int);
insert into t1 (i) select 666 from t2;
insert into t1 (j) select 666 from t2;
select * from t1 where i = 666 or j = 666;

-- drop and recreate t1
drop table t1;
create table t1 (i int, j int);

-- Negative test cases - column references in values clause
insert into t1 values(1, c1);
insert into t1 values("asdf asdf", 2);

-- Negative test case - syntax error
insert into t1 values;

-- Too many values in values clause
insert into t1 values(1,1,1);
-- insert select with too many result columns in select
insert into t1 select 1, 2, 3 from t2;
-- multiple instances of same column in colum list
insert into t1 (i, i) values(2,2);

-- target column list size != source size
insert into t1 (i, j) values(1);
insert into t1 (i) values (1, 2);

-- Negative test cases - column name not specified
insert into t1 select 666 from t2;

-- target table in source - deferred mode
insert into t1 values (1,1);