Jay,

This is the code for the set based update statement:

declare @row int, @increment int, @start datetime, @update_count int

set @start = getdate()

set @row = 1

set @increment = 5000

set @update_count = 0

set rowcount 0

while @row <= 5000 begin

update [claims_2002] with (tablockx)

set mdl_yr = left(col001,4), vin_cd = col002, sls_cntr = col003, veh_line = col004, plant_cd = col005, maint_dt = dbo.fn_AwsDateCheck(col006), prodn_dt = dbo.fn_AwsDateCheck(col007), wrty_sta = dbo.fn_AwsDateCheck(col008), orig_wrt = dbo.fn_AwsDateCheck(col009), wcc_cd = col011, tot_cost = dbo.fn_AwsClaimLocalCurrencyCost(col026,col012)

,lbr_cost = dbo.fn_AwsClaimLocalCurrencyCost(col026,col013), mtrl_cos = dbo.fn_AwsClaimLocalCurrencyCost(col026,col014), prt_num0 = col015, prt_num_ = col016, prt_num1 = col017, cpsc_6_c = col018, clm_cnt_ = col019, veh_cnt_ = col020

,sls_regi = col021, load_dt = dbo.fn_AwsDateCheck(col022), fcc_auth = col023, rpr_dt = dbo.fn_AwsDateCheck(col024), cust_con = col025, rpr_cntr = col026, milge = col027, mkt_deri = col028, adj_amt = col029, adj_rsn_ = col030

,cond_cd = col031, fcc_bin_ = col032, fcc_milg = col033, fcc_regi = col034, fcc_rpt_ = col035, fcc_term = col036, lbr_hrs = col037, prt_mrku = col038, prt_typ_ = col039, rpr_dlr_ = col040

,rpr_dlr0 = col041, doc_num = col042, funds_ty = dbo.fn_AwsClaimCurrency(col026)

from tempdb..clm02_new

where [claims_2002].clm_key = tempdb..clm02_new.col010

and tempdb..clm02_new.rec_id between @row and (@row + @increment)

set @update_count = @update_count + @@rowcount

set @row = @row + @increment + 1

end

print @update_count

print datediff(second,@start,getdate())

print @update_count/datediff(second,@start,getdate())

Functions:

/* Converts US$ Cost into Local Currecny cost for AWS claims */

CREATE FUNCTION [dbo].[fn_AwsClaimLocalCurrencyCost] (@country_repaired varchar(25), @usd_cost decimal (25,2) )

RETURNS decimal (25,2) AS

BEGIN

return ( select @usd_cost * funds_fa

from claims_fundrep_new

where rpr_cntr = @country_repaired

)

END

/* Checks the validity of date fields from AWS downloads */

CREATE FUNCTION [dbo].[fn_AwsDateCheck] (@date smalldatetime)

RETURNS varchar(25) AS

BEGIN

declare @output_date varchar(25)

if @date > '1950-01-01' and isDate(@date) = 1 set @output_date = @date

else set @output_date = null

return @output_date

END

/* Returns local currency for AWS claims */

CREATE FUNCTION [dbo].[fn_AwsClaimCurrency] (@country_repaired varchar(25) )

RETURNS varchar(25) AS

BEGIN

declare @output_Field varchar(25)

if @country_repaired = 'USA' set @output_field = 'USD'

else select @output_Field = funds_ty

from claims_fundrep_new

where rpr_cntr = @country_repaired

return (@output_field)

END

I'm still working on this so cosmectically it may not be at it's best (such as column names in the temporary tables)

This is a 'weekly' update job although it can run at any time.

Any ideas for improvements?

Jeremy