Re: [sqlite] Split a table

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