Re: Bulk inserts into two (related) tables

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(dot)klaver(at)aklaver(dot)com