Subject Re: [firebird-support] insert into select ... union ...
Author Helen Borrie
At 01:09 PM 25/10/2004 -0700, you wrote:

>insert into temp_hl7_export_people (person_id)
>select temp_hl7_export_tasks.person_id from temp_hl7_export_tasks
>select temp_hl7_export_pharm.person_id from temp_hl7_export_pharm
>select from people where person_id != 0 and last_update_stamp >
>I get 'token unknown: union' at the first one.

Oh, OK, because you can't use anything but a straight SELECT statement for
this...but what I was suggesting was just to use the unioned set directly,
i.e. why use a temporary table?

>I can run any chunk of this just fine. I'm using
>this to then join back against various tables during an export (multiple
>parallel datasets,) so I'd
>really rather the temp_hl7_export_people table be entirely unique before I
>start doing that, to
>avoid working the engine harder than needed. (The two first temp tables
>have their own logic
>applied ahead of time.) The field types match, number of fields match ...
>thus my question about
>unions during 'insert into'.

Well, a way to do that would be to create a view of the union and do your
insert from that. And, again, unless you specify UNION ALL, you won't get
any duplicates.

But you really don't need the temporary table at all, since every row in
the view is already a distinct person_id, every time you select from it,
ready to be used on the left side of the join