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