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