T-SQL SQL Server 2000

If you can show us your insert query then might expert(s) can help you...
this query is perfect.

Source File : Flat Files.

Inserting through Bulk Insert stmt to the destination table.

you might try:

where a.callednb = like b.prefixcode + '%'
  and len(a.callednb) >= 11

update a
set a.trff_pfx = b.prefixcode,A.Destination=B.destcode   from #dest_asr  a , w_prefix B
where substring(a.callednb, 1,11) = b.prefixcode
and b.prefixlen = 11 and a.trff_pfx is null


There are around 15 Case items for updating the value.

Means, there are substring(a.callednb, 1,11) 15 update statemes, like

update a
set a.trff_pfx = b.prefixcode,A.Destination=B.destcode   from #dest_asr  a , w_prefix B
where substring(a.callednb, 1,1) = b.prefixcode
and b.prefixlen = 11 and a.trff_pfx is null

update a
set a.trff_pfx = b.prefixcode,A.Destination=B.destcode   from #dest_asr  a , w_prefix B
where substring(a.callednb, 1,2) = b.prefixcode
and b.prefixlen = 11 and a.trff_pfx is null

.
.
.
.
.
.
update a
set a.trff_pfx = b.prefixcode,A.Destination=B.destcode   from #dest_asr  a , w_prefix B
where substring(a.callednb, 1,15) = b.prefixcode
and b.prefixlen = 11 and a.trff_pfx is null

in such event function feasable not posible for give the update statement

Looks to me like you're just trying ultimately to get the best (i.e. longest) match (? right).
Maybe try to do that in one UPDATE instead of 15, like so:

UPDATE a
SET
    a.trff_pfx = b.prefixcode,
    a.Destination = b.destCode
FROM #dest_asr a
INNER JOIN (
    SELECT a2.callednb, MAX(b2.destcode) AS destcode
    FROM #dest_asr a2
    INNER JOIN dbo.w_prefix b2 ON
        a2.callednb LIKE b2.prefixcode + '%' 
    AND a2.trff_pfx IS NULL
) AS bestMatch ON
    a.callednb = bestMatch.callednb
INNER JOIN dbo.w_prefix b ON
    b.destcode = bestMatch.destcode

Select allOpen in new window

Hi Scott

getting below error while executing the query.

Column '#dest_asr.callednb' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Taking more time for huge volume of tables.

The script okay for less size of the table.

Do you have an index on a2.callednb?  Best would be clustered, but even a non-clus index should help a lot.