Re: Moving data from one set of tables to another?

What do you want for your end product? Are the old tables empty
after you put the data into the new tables?

Carol

On Sep 18, 2008, at 3:02 PM, Howard Eglowstein wrote:

> I have three tables called 'data_a', 'data_b' and 'data_c' which
> each have 50 columns. One of the columns in each is 'id' and is
> used to keep track of which data in data_b and data_c corresponds
> to a row in data_a. If I want to get all of the data in all 150
> fields for this month (for example), I can get it with:
>
> select * from (data_a, data_b, data_c) where data_a.id=data_b.id
> AND data_a.id = data_c.id AND timestamp >= '2008-09-01 00:00:00'
> and timestamp <= '2008-09-30 23:59:59'
>
> What I need to do is execute this search which might return several
> thousand rows and write the same structure into 'new_a', 'new_b'
> and 'new_c'. What i'm doing now in a C program is executing the
> search above. Then I execute:
>
> INSERT INTO data_a (timestamp, field1, field2 ...[imagine 50 of
> them]) VALUES ('2008-09-01 00:00:00', 'ABC', 'DEF', ...);
> Get the ID that was assigned to this row since 'id' is a serial
> field and the number is assigned sequentially. Say it comes back as
> '1'.
> INSERT INTO data_b (id, field1, field2 ...[imagine 50 of them])
> VALUES ('1', 'ABC', 'DEF', ...);
> INSERT INTO data_c (id, field1, field2 ...[imagine 50 of them])
> VALUES ('1', 'ABC', 'DEF', ...);
>
> That moves a copy of the three rows of data form the three tables
> into the three separate new tables.
> From the original group of tables, the id for these rows was, let's
> say, '1234'. Then I execute:
>
> DELETE FROM data_a where id='1234';
> DELETE FROM data_b where id='1234';
> DELETE FROM data_c where id='1234';
>
> That deletes the old data.
>
> This works fine and gives me exactly what I wanted, but is there a
> better way? This is 7 SQL calls and it takes about 3 seconds per
> moved record on our Linux box.
>
> Any thoughts or suggestions would be appreciated.
>
> Thanks,
>
> Howard
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice