How can I populate the COMM_EO_COMPOSITION table with a list of species in a batch update?

There's several ways do it. These steps assume that the species elements are already in your database. All you want to do is to associate an existing EST with an existing EO.

If you want to associate the same species EST with many different EOs, here's one way to do it:
#1. Make a working list of the EOs that you want to associate with the species.

#2. Use a SQL+ insert statement to create entries in the COMM_EO_COMPOSITION table. For example if you wanted to associate EST #123456 with all of the EOs on working list "My Favorite EOs". It would look something like this:

insert into COMM_EO_COMPOSITION (eo_id, element_taxon_subnatl_id)
select eo.eo_id, 123456
from eo,
working_list wl
working_list_eo_data wled
where wl.working_list_name = 'My Favorite EOs' and
wl.working_list_id = wled.working_list_id and
wled.data_id = eo.eo_id;

The example does not include any values for any other columns in the COMM_EO_COMPOSITION table, but if you have that information, you can add them in. This just builds the bare bones entries in that table. Don't forget to commit after adding the rows.

If you want to associate many different EOs with many different ESTs, you'll need to build a spreadsheet to match up each eo_id with the appropriate element_subnational_id. You can get your list of eo_ids and element_subnational_ids through queries saved to text files.

#1. Column A would contain: insert into COMM_EO_COMPOSITION (eo_id, element_taxon_subnatl_id) values ( . Then fill down for as many rows as you need

#2. Column B would contain a list of your eo_ids. If you have a list of eo_ids from a query, cut and paste them into this column.

#3 Column C would contain a comma. Then fill down for as many rows as you need

#4. Column D would contain a list of the matching element_subnational_ids. If you have a list of element_subnational_ids from a query, cut and paste them into this column. Make sure they are in synch with the eo_ids in column B.

#5. Column E would contain a ); Then fill down for as many rows as you need.

#6. You should then have insert statements for each eo/est pair. Copy and paste these statements into SQL+ and run them. Don't forget to commit.

Triggers should take care of supplying the primary key value for comm._eo_composition_id. It should also update the next_seq table for you as well.

These are just two of many ways to get the data in this table. You might have to correct some syntax on the above statements since I can't test them out on a real database. But these examples should give you the general idea.