sql server - Avoiding cursors to update many records using a trigger -
i have table on 1 million records. initially, table empty, use bulk insert add these records database. have after insert trigger use update initialvalue field in table. value of initialvalue calculation of specific variables in table (my_data_db) summed across records. using values of v1, v2, etc. columns column names in table my_data_db.
i know it's poor practice, way know how calculation of every single row using cursor. million records really, slow.
here's example of table have trigger on:
table: test3 rowid v1 v2 v3 combo initialvalue 1 null m170_3 m170_4 c null 2 m170_2 m170_3 m170_4 abc null 3 m170_2 m170_3 null ab null ... my trigger is:
create trigger [dbo].[trig_update_test3] on [dbo].[test3] after insert begin declare @sql varchar(max) declare @v1 varchar(20) declare @v2 varchar(20) declare @v3 varchar(20) declare @combo varchar(30) declare mycursor cursor select v1, v2, v3, combo inserted open mycursor fetch next mycursor @v1, @v2, @v3, @combo while @@fetch_status = 0 begin if( @v1 not null or @v2 not null or @v3 not null) begin set @sql = 'declare @finalvalue decimal(18, 15);' set @sql = @sql + 'update test3 set initialvalue = (select cast(sum(' if(@v1 not null) begin set @sql = @sql + 'case ' + @v1 + ' when 1 1 when 2 .75 when 3 .25 when 4 .1 end * ' end if(@v2 not null) begin set @sql = @sql + 'case ' + @v2 + ' when 1 1 when 2 .75 when 3 .25 when 4 .1 end * ' end if(@v3 not null) begin set @sql = @sql + 'case ' + @v3 + ' when 1 1 when 2 .75 when 3 .25 when 4 .1 end * ' end set @sql = @sql + 'resp_weight / 4898.947426) float) dbo.my_data_db) combo = ''' + @combo + ''';' execute(@sql) end fetch next mycursor @v1, @v2, @v3, @combo end close mycursor deallocate mycursor end after trigger runs, test3 table like:
table: test3 rowid v1 v2 v3 combo initialvalue 1 null m170_3 m170_4 c 0.138529 2 m170_2 m170_3 m170_4 abc 0.683190 3 m170_2 m170_3 null ab 0.014923 ... is there way can accomplish without use of cursor?
yes. can single update - from statement after bulk insert:
update t3 set initialvalue = t.mysum test3 t3 cross apply (select sum( case t3.v1 when 'm170_2' case d.m170_2 when 1 1 when 2 .75 when 3 .25 when 4 .1 else 1 end end * case t3.v1 when 'm170_3' case d.m170_3 when 1 1 when 2 .75 when 3 .25 when 4 .1 else 1 end end * case t3.v1 when 'm170_4' case d.m170_4 when 1 1 when 2 .75 when 3 .25 when 4 .1 else 1 end end * d.resp_weight / 4898.947426) mysum my_data_db d d.combo = t3.combo) t t3.v1 not null or t3.v2 not null or t3.v3 not null to trigger, need minor change:
update t3 set initialvalue = t.mysum test3 t3 -- here's change inner join inserted on i.rowid = t3.rowid cross apply (select sum( case t3.v1 when 'm170_2' case d.m170_2 when 1 1 when 2 .75 when 3 .25 when 4 .1 else 1 end end * case t3.v1 when 'm170_3' case d.m170_3 when 1 1 when 2 .75 when 3 .25 when 4 .1 else 1 end end * case t3.v1 when 'm170_4' case d.m170_4 when 1 1 when 2 .75 when 3 .25 when 4 .1 else 1 end end * d.resp_weight / 4898.947426) mysum my_data_db d d.combo = t3.combo) t t3.v1 not null or t3.v2 not null or t3.v3 not null
Comments
Post a Comment