CODEWITHSUNDEEP
sql-server
PS078
PS078

Reputation: 461

How to concatenate string of a column and update the string in another table at the same time with using Cursor

I have a requirement wherein I have a #ValidationList table in which we have recorder all the validation error found in the imported data.

Following is the snapshot of data in #ValidationList table:

Id  ErrorMsg
1   Asset  # Unknown 
1   Unique Name and code is needed
2   Unique Name and code is needed
2   Asset  # Unknown 
3   Asset  # Unknown 
3   Unique Name and code is needed
4   Unique Name and code is needed
4   Asset  # Unknown

Now all these errors strings need to be updated in the original staging table #ImportedStagingData after concatenated with delimiter for same IDs.

As of now we are using cursor to update the errors in the original table:

declare Errors Cursor for
        Select DISTINCT Id from ##ValidationList

    Open Errors

    Fetch Next from Errors into @Id      

    while @@Fetch_status = 0      
        begin

            set @ErrorMessage=null
            SELECT @ErrorMessage = COALESCE(@ErrorMessage, '') + ' / ' + ErrMsg
                        FROM (SELECT ErrorMessage as ErrMsg
                              FROM ##ValidationList
                              WHERE Id = @Id) [ErrMsg]

            update #ImportedStagingData 
            set ErrorMessage = @ErrorMessage
            where Id = @Id

            Fetch Next from Errors into @Id
        end

    close Errors
    deallocate Errors

Is there any way we can remove this cursor implementation and use SET BASED approach for better performance. Kindly suggest.

Upvotes: 1

Views: 56

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

For Concatentation use XML + STUFF trick:

UPDATE i
SET ErrorMessage = STUFF( (SELECT '/' + v.ErrorMessage 
                             FROM ##ValidationList v
                             WHERE v.Id = i.Id
                             FOR XML PATH('')), 
                            1, 1, '')
FROM #ImportedStagingData i;

This solution is SET based. You should avoid loop/cursor because ROW by ROW processing is very inefficient.

Upvotes: 2

Related Questions