insert.sql

derby database source code.good for you.

insert into t1 values (2,2);
delete from t2;
insert into t2 select * from t1;

autocommit off;

select * from t1;
insert into t1 select t1.* from t1, t2;
select * from t1 order by 1, 2;
rollback;

insert into t1 (i) select (select i from t1 where i = 1) from t2;
select * from t1;
rollback;

insert into t1 (i) select 1 from t2 where 1 = (select i from t1 where i = 1);
select * from t1;
rollback;

insert into t1 select * from (select * from t1) a;
select * from t1;
rollback;

-- bug 5638
insert into t1 select * from t2 union select * from t1;
select * from t1;
rollback;

-- bug 5638
insert into t1 select * from t2 union select * from (select * from t1) a;
select * from t1;
rollback;

-- single-row deferred insert
insert into t1 select * from t1 where i = 1;
select * from t1;
rollback;

-- zero-row deferred insert - degenerate case
insert into t1 select * from t1 where i = 17;
select * from t1;
rollback;

-- insert-select with ? parameters
prepare i1 as 'insert into t1 (j, i) select 101,102 from t1 where i = ?';
execute i1 using 'values (1)';
select * from t1;
rollback;


autocommit on;

-- test atomicity of multi row inserts
create table atom_test_target (c1 smallint);
create table atom_test_source (c1 smallint);
insert into atom_test_source values 1, 30000,0, 2;

-- overflow
insert into atom_test_target select c1 + c1 from atom_test_source;
select * from atom_test_target;

-- divide by 0
insert into atom_test_target select c1 / c1 from atom_test_source;
select * from atom_test_target;

-- Derby-34
create table tchar( i int, c char(1) for bit data default x'02');
create table tchar1 (i int, c char(5) for bit data default x'2020202020',
			v varchar(5) for bit data default x'2020',
			l long varchar for bit data default x'303030');

drop table tchar;
drop table tchar1;

-- insert various numeric types into other numeric types
create table i1 (i int, t int, s smallint, l bigint, r real, dp double, dc dec);