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.klaver@aklaver.com