Re: Bulk inserts into two (related) tables

On 5/22/19 7:38 AM, Rich Shepard wrote:
> On Wed, 22 May 2019, Jeremy Finzel wrote:
> 
>> There's absolutely no need to use anything beyond SQL here, though you
>> could if you want to.
> 
> Jeremy,
> 
> This is a new experience for me so I didn't think of a SQL solution.
> 
>> I really wonder how much we are just talking past each other simply 
>> because
>> we don't know what your data looks like, so we can't show you how our
>> examples apply to your use case. If you provided a sample scrubbed data
>> file, this whole thread probably would have been much shorter :).  Can 
>> you
>> do that?
> 
> Not necessary; see below. Also, these data come from a regulator and
> provided as an Excel spreadsheet. If they were extracted from a database
> then that was very poorly designed because there's no consistency in how
> fields/columns are formatted. This requires manual cleaning.
> 
> Each row in the source file (exported from the spreadsheet as .csv and
> renamed to .txt for processing in emacs and awk) is a mixture of attributes
> that belong in either or both of the organization and people tables in my
> database. An awk script will extract the appropriate fields for each table.

So does the people data have an organization attribute?

If so why not just assign the org_id while cleaning up the data?

> 
>> You told Francisco that the data file does not have a unique org name 
>> that
>> could be used as a unique organization identifier. However you seem to
>> have contradicted that by responding favorably to this solution:
> 
> The org_name is not the PK; the org_id is. This was assigned by postgres
> when the original rows were inserted. Now, I can add the org_id in the
> values to be inserted as I know the maximum org_id number in that table.
> 
>> INSERT INTO PEOPLE (id, name, org_id) VALUES (1, ‘Bob’, (SELECT org_id 
>> FROM
>> org WHERE org_name=‘Main Office’))
> 
> Question: do I use this same syntax for each row to be inserted or can I
> make it one long insert statement by separating the parenthesized values
> with commas as I do when I update multiple rows in a table?
> 
> Thanks very much,
> 
> Rich
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com