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]
-
