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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"