Ulrich Schöbel a écrit :
As this list has excellent SQL wizards, I hope someone can help
me on the problem to split a table into two.
I have something like this:
create table org (
a text,
b text,
c text,
d text
);
I need to split it into rwo tables as follows:
create tbl_a (
a_id integer not null autoincrement,
a text,
b text
);
create tbl_b (
b_id integer,
c text,
d text
);
with b_id corresponding to a_id and a_id autogenerated.
I know how to do this within a tcl script, but I need a way
in pure SQL. Is it at all possible?
Thanks for your help
Ulrich
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
Hello,
Look at this example with triggers...
regards,
Yves.
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .read test.sql
.echo ON
BEGIN TRANSACTION;
create table org (
a text,
b text,
c text,
d text
);
create table tbl_a (
a_id integer PRIMARY KEY NOT NULL,
a text,
b text
);
create table tbl_b (
b_id integer PRIMARY KEY NOT NULL,
c text,
d text
);
CREATE TRIGGER [tbl_a_after_insert] AFTER Insert ON tbl_a
BEGIN
INSERT into tbl_b (b_id, c, d)
SELECT new.a_id, org.c, org.d FROM org
WHERE org.a = new.a AND org.b = new.b;
END;
insert into org values('aaa','bbb','ccc','ddd');
insert into org values('111','222','333','444');
insert into org values('abc','def','999','888');
COMMIT;
select * from org;
aaa|bbb|ccc|ddd
111|222|333|444
abc|def|999|888
insert into tbl_a select null, a, b from org;
select * from tbl_a;
1|aaa|bbb
2|111|222
3|abc|def
select * from tbl_b;
1|ccc|ddd
2|333|444
3|999|888
.echo OFF
sqlite>