"Dave M" <mr_mensch2@hotmail.com> wrote in message
news:E925FD6C-E17A-4B77-9355-83A0372C33F0@news.elevatesoft.com...
> "Robert" <rkaplan@AdvantSoft.com> wrote:
> Shure it is. I do it all the time.
>
> Robert
> --------------------------------------------------------------------------
------------------------------------
>
> I am in the process of converting an app to DBISAM. (First time user.)
> Didn't even try the SELECT with UNION.
> The code was already done for the streaming, so that was used.
> Also, I went by page 205 of the manual (dbisam4d2006.pdf):
>
> "The INSERT statement can use a single SELECT statement as the source for
the new rows,
> but not multiple statements joined with UNION."
>
> Thanks, the SELECT & UNION does work.
>

I'm not much of a manual reader, I guess I tried it and it worked
However, I guess it would not be that much different to do a script and
INSERT each SELECT individually.

You need to be sure you CAST unused fields appropriately, especially
decimals, but other than that, it seems to work just fine.

I have an app where I ended up with an amazingly complex select with
multiple UNIONs pulling data from different tables, all inserting into the
same table. An SQL masterpiece full of IF statements, but had I known at
first that it would get so complex, I would have coded it in Pascal.

Robert

Dave,

<< 1) I assume this is by design?  >>

Yes.   Irregardless of the INTO clause, DBISAM always treats the last SELECT
SQL statement in a script as the result set and opens it.  If you were to
tag a non-SELECT statement on to the existing SELECT statement, then DBISAM
would automatically close the INTO result set.

<< 2) If so, could this be changed in the future? >>

It is with the next version (ElevateDB).  It uses the CREATE TABLE LIKE
<subquery> construct instead of the INTO for creating tables from other
tables or queries.   This was done to remove any ambiguity with stored
procedures that use INTO for putting scalar values into procedure variables.

--
Tim Young
Elevate Software
www.elevatesoft.com