Hi

What about something like this.

declare @inc int = 0

create table #BATCH (

Number int,

BATCH varchar(2)

)

while (@inc < 36000)

begin

insert into #BATCH

select @inc,

case

WHEN @inc BETWEEN 10000 AND 10999 THEN 'A'

WHEN @inc BETWEEN 11000 AND 11999 THEN 'B'

WHEN @inc BETWEEN 12000 AND 12999 THEN 'C'

WHEN @inc BETWEEN 13000 AND 13999 THEN 'D'

WHEN @inc BETWEEN 14000 AND 14999 THEN 'E'

WHEN @inc BETWEEN 15000 AND 15999 THEN 'F'

WHEN @inc BETWEEN 16000 AND 16999 THEN 'G'

WHEN @inc BETWEEN 17000 AND 17999 THEN 'H'

WHEN @inc BETWEEN 18000 AND 18999 THEN 'I'

WHEN @inc BETWEEN 19000 AND 19999 THEN 'J'

WHEN @inc BETWEEN 20000 AND 20999 THEN 'K'

WHEN @inc BETWEEN 21000 AND 21999 THEN 'L'

WHEN @inc BETWEEN 22000 AND 22999 THEN 'M'

WHEN @inc BETWEEN 23000 AND 23999 THEN 'N'

WHEN @inc BETWEEN 24000 AND 24999 THEN 'O'

WHEN @inc BETWEEN 25000 AND 25999 THEN 'P'

WHEN @inc BETWEEN 26000 AND 26999 THEN 'Q'

WHEN @inc BETWEEN 27000 AND 27999 THEN 'R'

WHEN @inc BETWEEN 28000 AND 28999 THEN 'S'

WHEN @inc BETWEEN 29000 AND 29999 THEN 'T'

WHEN @inc BETWEEN 30000 AND 30999 THEN 'U'

WHEN @inc BETWEEN 31000 AND 31999 THEN 'V'

WHEN @inc BETWEEN 32000 AND 32999 THEN 'W'

WHEN @inc BETWEEN 33000 AND 33999 THEN 'X'

WHEN @inc BETWEEN 34000 AND 34999 THEN 'Y'

WHEN @inc BETWEEN 35000 AND 35999 THEN 'Z'

WHEN @inc <= 9999 THEN '' end

set @inc = @inc +1;

end

Create index idx_Number on #BATCH (Number)include (BATCH)

INSERT INTO #TMP1A

(ACCTCORP,

HOUSE,

CUST,

BADATE,

AMOUNT,

ADJRSN,

BATCH,

CTLGRP,

POSTFLAG)

SELECT

ACCTCORP,

HOUSE,

CUST,

BADATE,

AMOUNT,

ADJRSN,

CASE WHEN b.BATCH is not null then b.BATCH + RIGHT(a.BATCH,3)

WHEN b.BATCH)<= 9999 THEN a.BATCH

ELSE '' END AS 'DDP_BATCH_#',

BATCH AS [ORIG_BATCH_#],

--BATCH,

CTLGRP,

CASE WHEN POSTFLAG = ' ' THEN 'No'

ELSE 'Yes' END AS POSTFLAG

from <Table> a

left JOIN #BATCH b on a.BATCH =b.BATCH

Please feel free to ask for more detail in the explanation.