問題描述

我有下面的插入查詢，它從 OriginalData 表中選擇記錄，其中一切都是數(shù)據(jù)類型 nvarchar(max) 并將其插入到具有特定列的臨時表中定義，即 MainAccount 是 INT 類型.

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.

我正在逐行插入，因為如果 OriginalData 表中有一條記錄，其中 MainAccount 值為Test"，它顯然會導致轉換錯誤并且插入將失敗.begin try 塊用于更新包含錯誤的表.

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

推薦答案

我沒有這樣做，而是通過在要轉換為非字符串列的每一列上使用 TRY_PARSE() 或 TRY_CONVERT() 來處理此問題.

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.

這篇關于在循環(huán) SQL 查詢中捕獲多個錯誤的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！