Re: Maximun number of unions in a single query

Andreas Sheriff wrote:
> As for mendicant, yes I did mean the lexical definition. To call someone an
> a$$h0le does not abide by the geek's book of insults.

Yeah, but that book does not count on Usenet as you should well know. Or have you forgotten about Godwin's Law? Sheez!

> I'm working with an application that does multiple inserts into a table.
<snipped>
> Here's what I want to accomplish:
>
> insert into mytable(col1, col2, col3, col4)
> select :1, :2, :3, :4 from dual union all
> select :5, :6, :7, :8 from dual union all
> select :9, :10, :11, :12 from dual union all
> select :13, :14,:15, :16 from dual union all
> etc...
>
> It seems to me that this method of inserting data into a table using one
> insert statement
> would be a hell-of-a-lot better than doing multiple insert statement between
> <cfquery></cfquery>.

Nope. I cannot see that as being any faster. How much parsing is that, compare to a single hard parse of:
insert into mytable( :col1, :col2, :col3, :col4)

Followed by a soft parse (assuming no statement handle re-use on the client side) for every following INSERT statement.

Better yet would be a single parse and passing the bind variable data as an array (for bulk binding on the SQL engine side).

Also not forgetting the execution path. The INSERT is still called/execxuted x times irrespective. Only with the UNIONs, there is also a SELECT that is called x times. Why the SELECT overhead when you can simply execute the INSERT x times?

Please say you agree or you will force me to spend time setting upo a test case to show you are wrong.

> Now you know the reasoning behind my question and now you *all* can stop the
> flaming, or have I poured kerosene on the whole situation?

Flaming!? This is not even the size of small birthday candle flame.

--
Billy