Hi Yves, hi Filip, thanks a lot for your help. You solved my problem and I learned something new.
Kind regards Ulrich Am Wednesday 11 July 2007 20:00 schrieb [EMAIL PROTECTED]: > 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> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------