data upload

[Martino165]

General questions: Can you upload a flat file into multiple related
tables?
If so... How can you make sure those are not duplicates.

for example: table 1- classes table 2- teachers table 3-
schedule
table 1 fields: class number, class name, class desc.
table 2 fields: teach last name, teacher first name, teacherID
table 3 fields: class number, teacherID, class time

I think these example tables have obvious relationships... If the input
flat file is just those listed, but specific fields would not be known
to the person creating the input file (e.g. teacherID).

therefore the flat file would just look like:
last name, first name, class name, class time

If the teacher is all ready listed I would just want the proper
associations to be kept in table 3 eventhough the auto ID #'s would not
be in the flat file.

If the teachers name is not listed I would want it to be added to the
teacher table and the schedule table.

Is this possible or do the arbitrary ID #s need to be known.

Any input would be greatly appreciated.

 

[gee664]

Do not use the ID field as the primary key. Rather, the PK should be
the field (or fields) that you do not want duplicated. BUT you can
still use the ID field to link to another table.

I would load the flat file 3 times, once for each table, starting with
the parent record tables. The PK for each table will prevent duplicate
records from being added.

 

[Martino165]

thanks for the reply..

if the information is loaded separately how does it keep its
relationships intact.

Or would you upload the PK/FK fields each time?

 

[gee664]

to populate a child table,

join the flat file and the parent table in an 'append query' to append
to the child table.

 

[gee664]

to populate a child table,

join the flat file and the parent table in an 'append query' to append
to the child table.

 

[Pieter Wijnen]

load the data into a "load" table & write code to distribute the data to
your tables
(works for me ;-) ).
This approach does mean programming the "loaded" data - but the upside is a
lot of reusable code....

Pieter

 

[Martino165]

would you be willing to share some of that code.. I have seen examples
of "holding" tables. I would also like to be able to run a error
checker, before uploading to the final tables.

Any starting point, reference, info, code, etc would all be of help to
me..
thanks