Import data accross tables

Hi, I wondered if it is conceptually possible to import data from SD files into several database tables simultaneously. For example, if there is a table with Compounds and a table with Suppliers, and a many-to-one relationship that joins these through the CdId field, then it would be very useful if one could import the structures into the Compounds table and Supplier data into the Supplier table at the same time. Unless I'm missing something obvious (not unlikely ;-), this currently has to be done in several steps: first import the structures in the Compounds table; export everything again with the newly created CdId added; then import the CDId's and relevant data into the Supplier table.

Regards,

HomerE

Hi, yes we do have plans for this sort of feature, but it is quite complex as there are several scenarios that need to be covered.

Your suggested workaround will work fine, but there is probably a simpler solution.

1. load the SD file into IJC as usual

2. Create the other table to hold the supplier data, and the relationship between the tables. This table will intially be empty but you should add the fields that are needed to hold the data that you wnat to transfer from the structure table.

3. Connect to the database using a SQL administration tool (e.g. SQL Plus for Oracle, IJ for Derby etc. or use the Database Explorer in IJC that can be found in the Services window.)

4. Transfer the data from the structure table using SQL. e.g. something like this:

insert into SUPPLIER (CD_ID, A, B, C) (select CD_ID, A, B, C from STRUCTURES)

This one step will transfer all your data.

5. Reconnect IJC and delete the now redundant columns from the structure table).

 

One word of warning - don't use this approach to alter structures in a JChem table as these must only be modified through the JChem API, not directly with SQL. But it works fine for non-structure tables.

 

Let me know if you need more help with this.

 

Tim