|
|
Posted by mike on 09/22/06 20:11
I'm looking for an efficient way to populate derived columns when I
insert data into a table in SQL Server. In Informix and PostgreSQL
this is easily done using the "for each row..." syntax, but all I've
been able to come up with for SQL Server is the following:
create table testtrigger(id integer unique, b integer, c integer)
go
create trigger testtrigger_ins on testtrigger
for insert as
update testtrigger set c = (select ...some_function_of_b... from
testtrigger t1,inserted t2
where t1.id = t2.id)
where id in (select id from inserted);
go
where id is testrigger's unique id field, and c is a field derived from
b.
This seems terribly inefficient since each insert results in an extra
select and update. And if the table is large and unindexed (which it
could be if we are bulk loading) then I would imagine this would be
very slow.
Are there any better ways of doing this?
Many thanks,...
Mike Dunham-Wilkie
Navigation:
[Reply to this message]
|