Re: Manual query vs trigger during data load

On 2024-09-14 00:54:49 +0530, yudhi s wrote:
> As "thiemo" mentioned , it can be done as below method, but if we have
> multiple lookup tables to be populated for multiple columns , then , how can
> the INSERT query be tweaked to cater the need here?

Just use a join:
    insert into target(val1, val2, val3, val4)
    select :param1, cfgA.substA, :param3, cfgB.substB
    from cfgA, cfgB
    where cfgA.keyA = :param2 and cfgB.keyB = :param4

Or use a CTE per lookup which might be more readable:

    with cA as ( select substA from cfgA where keyA = :param2 ),
         cB as ( select substB from cfgB where keyB = :param4 )
    insert into target(val1, val2, val3, val4)
    select :param1, cA.substA, :param3, cB.substB
    from cA, cB

However, I agree with Rob here. It's probably better to do the
substitution in Java.

        hp


-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"