On 5/21/19 9:56 AM, Rich Shepard wrote:
I'm cleaning and formatting a 800-line data file to be inserted into a
database. Some of the input file fields will be inserted into an
'organizations' table letting postgres assign sequential org_id numbers.
Other fields will be inserted into a separate 'people' table associated
with
each organization. The people table insert allows postgres to assign the
person_id number and this table's foreign key, people.org_id ==
organizations.org_id.
I could insert all new rows into the organizations table, then produce a
list of the org_id and org_name to manually insert the foreign key in the
related people table. Time and effort intense.
Is there a way to insert the two tables sequentially without manually
adding
the organizations.org_id to the appropriate foreign key column
(people.org_id)
of the people table?
Well you are not going to know the org_id until the organization table
is loaded, which means something like:
1) First run through file load the organizations table.
2) Build a mapping of org_id to organization.
3) Run through data file again and load people data using the mapping in
2) to provide the people.org_id.
Rich
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
