Thread: adding data to tables with sequences

Hello,

I'm trying to add some data into tables in order with sequences using
an order by command and I wonder if anyone could give me advice on
a better way to do it.

I'm trying to get data from a temporary table into several tables. The
tables are :

temp_data
  temp_id  integer primary key default nextval('temp_id_seq') not null,
  marker varchar(128),
  accession varchar(128),
  value integer

data_type
  type_id  integer primary key default nextval('type_id_seq') not null,
  description varchar(128)

data
  data_id integer primary key default nextval('data_id_seq') not null,
  type_id integer
foreign key (type_id) references data_type (type_id)

data_value
  data_id integer not null,
  element_number integer not null,
  value integer not null
primary key (data_id, element_number)
foreign key (data_id) references data (data_id)

I'm inserting data into the data_value table from the temporary table
and would like it ordered on the temp_id when inserting.

what I've tried is
INSERT INTO data_value (data_id, element_number, value)
SELECT d.data_id, nextval('element_seq'), t.value
FROM data d, temp_data t, data_type dt
WHERE d.type_id = dt.type_id
AND t.marker = dt.description
ORDER BY t.temp_id;

This works only the element_numbers using the sequences are in a
random order. The values seem to be inserted in the order of the
temp_id so if I do a select * from the table the are listed in the order I'd
like them in. (I need the element numbers in order based on temp_id
because they then relate to an accession in another table). I've been
able to get around this by then updating the data_value table with a
second sequence and setting the element_number to the new
sequence. This seems to reorder the element_number values, but it
seems to me this probably isn't the best way to solve the problem. Is
there another way to do this in without adding an accession_id field
to the data_value table? I've been asked to insert this data into an
existing database without altering the database structure. Any
suggestions would be much appreciated.

thanks much,
Jennifer

"Jennifer Lee" <jlee@scri.sari.ac.uk> writes:
> what I've tried is
> INSERT INTO data_value (data_id, element_number, value)
> SELECT d.data_id, nextval('element_seq'), t.value
> FROM data d, temp_data t, data_type dt
> WHERE d.type_id = dt.type_id
> AND t.marker = dt.description
> ORDER BY t.temp_id;

> This works only the element_numbers using the sequences are in a
> random order. The values seem to be inserted in the order of the
> temp_id so if I do a select * from the table the are listed in the order I'd
> like them in.

Well, yeah: you asked to ORDER BY temp_id, and that happens after the
SELECT-list expressions are computed.  You could probably get the
behavior you want with a two-level SELECT:

INSERT INTO data_value (data_id, element_number, value)
SELECT data_id, nextval('element_seq'), value
FROM
(SELECT d.data_id, t.value
 FROM data d, temp_data t, data_type dt
 WHERE d.type_id = dt.type_id
 AND t.marker = dt.description
 ORDER BY t.temp_id) ss;


            regards, tom lane