Catching multiple errors in loop SQL query

问题:

I have the below insert query which selects records from the OriginalData table where everything is of datatype nvarchar(max) and inserts it into the temp table which has specific column definitions i.e MainAccount is of type INT.

I am doing a row by row insert because if there is a record in OriginalData table where the MainAccount value is 'Test' the it will obviously cause a conversion error and the insert will fail. The begin try block is used to update the table with the error.

However if there are multiple errors on the same row I want to be able to capture them both and not just the first one.

TRUNCATE TABLE [Temp] 

DECLARE @RowId INT, @MaxRowId INT  

SET @RowId = 1

SELECT @MaxRowId = MAX(RowId) 
FROM [Staging].[FactFinancialsCoded_Abbas_InitialValidationTest]

WHILE(@RowId <= @MaxRowId)
BEGIN
BEGIN TRY  
    INSERT INTO [Temp] (ExtractSource, MainAccount, 
                        RecordLevel1Code, RecordLevel2Code, RecordTypeNo, 
                        TransDate, Amount, PeriodCode, CompanyCode)
        SELECT 
            ExtractSource, MainAccount,
            RecordLevel1Code, RecordLevel2Code, RecordTypeNo,
            TransDate, Amount, PeriodCode, DataAreaId
        FROM 
            [Staging].[FactFinancialsCoded_Abbas_InitialValidationTest]  
        WHERE 
            RowId = @RowId;

    PRINT @RowId;

END TRY  
BEGIN CATCH  

Update [Staging].[FactFinancialsCoded_Abbas_InitialValidationTest]  
Set ValidationErrors = ERROR_MESSAGE()
where RowId = @RowId  

END CATCH 
SET @RowId += 1;      
END

回答1:

Instead of doing it this way, I handle this by using TRY_PARSE() or TRY_CONVERT() on each column that I am converting to a non-string column.

If you then need to store the validation failures in another table, you can make a second pass getting all the rows that have a non-null value in the source table and a null value in the destination table, and insert those rows into your "failed validation" table.