問題描述

我想將一個表的行插入到另一個表中.為此，我有以下程序.

I want to insert rows of one table into another. For that I have the below procedure.

ALTER PROCEDURE LOADDATA_a
AS 
BEGIN

TRUNCATE TABLE STIDENT_A
DECLARE @SID INT
DECLARE @SNAME VARCHAR(50)
DECLARE @SUB VARCHAR(50)
DECLARE @MARKS INT

DECLARE LOAD_DATA CURSOR FAST_FORWARD FOR  
SELECT SID,SNAME,SUB,MARKS 
FROM student 

OPEN LOAD_DATA   
FETCH NEXT FROM LOAD_DATA INTO @SID,@SNAME,@SUB,@MARKS

WHILE @@FETCH_STATUS = 0   
BEGIN
    BEGIN TRANSACTION

    INSERT INTO STIDENT_A(SID,SNAME,SUB,MARKS) VALUES (@SID,@SNAME,@SUB,@MARKS)

    IF @@ERROR != 0
    BEGIN
        ROLLBACK TRANSACTION
        RETURN
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION
    END
    FETCH NEXT FROM LOAD_DATA INTO @SID,@SNAME,@SUB,@MARKS
END

CLOSE LOAD_DATA   
DEALLOCATE LOAD_DATA

END

每當一行插入失敗時，游標就會失敗并停在那里，但我需要它繼續.如何做到這一點?

Whenever a row fails to insert, the cursor fails and stops there, but I need it to continue. How to do that?

推薦答案

顯然，您在 ROLLBACK TRANSACTION 之后立即有一個 RETURN 語句.您是否嘗試將其刪除?

Apparently, you've got a RETURN statement immediately after ROLLBACK TRANSACTION. Have you tried just removing it?

盡管您也可以改用 TRY/CATCH 重寫正文，如下所示:

Although you could also rewrite the body using TRY/CATCH instead, like this:

...
WHILE @@FETCH_STATUS = 0   
BEGIN
    BEGIN TRY
        INSERT INTO STIDENT_A(SID,SNAME,SUB,MARKS)
        VALUES (@SID,@SNAME,@SUB,@MARKS);
    END TRY
    BEGIN CATCH
      -- this section must have some statement,
      -- so, why not log the erroneous data to the screen at least?
      PRINT @SID;
      PRINT @SNAME;
      PRINT @SUB;
      PRINT @MARKS;
      PRINT '';  -- an empty line as a delimiter
      -- or, perhaps, into a table?
      --INSERT INTO SomeFailLog (SID,SNAME,SUB,MARKS)
      --VALUES (@SID,@SNAME,@SUB,@MARKS);
    END CATCH;
    FETCH NEXT FROM LOAD_DATA INTO @SID,@SNAME,@SUB,@MARKS;
END;
...

但是，如果您知道具體是什么導致插入失敗，那么提出一條僅生成有效數據以進行插入的語句可能會更好.

But if you know what specifically may cause the inserts to fail, it might be even better to come up with a single statement that would produce only valid data to insert.

例如，如果問題是 student 中的某些 SID 已經存在于 STIDENT_A 中，而您需要忽略它們，您可以簡單地嘗試以下 您的程序:

For instance, if the issue is that some SIDs in student already exist in STIDENT_A and you need to omit them, you could simply try the following instead of your procedure:

INSERT INTO STIDENT_A (SID, SNAME, SUB, MARKS)
SELECT s.SID, s.SNAME, s.SUB, s.MARKS
FROM student AS s
LEFT JOIN STIDENT_A AS a ON s.SID = a.SID
WHERE a.SID IS NULL
;

如果您在傳輸數據時具體說明可能出現的問題，我們或許能夠幫助您找到最有效的解決方案.

If you specify what exactly may be the issue while transferring your data, we might be able to help you with finding the most efficient solution specifically for that.

更新處理評論

如果問題是STIDENT_A中SNAME的最大長度小于student中同名列的最大長度和一些值可能不適合，您可以簡單地使用過濾器(WHERE 子句)將插入的行限制為 SNAME 的實際長度不超過某個值的行:

If the issue is that the maximum length of SNAME in STIDENT_A is less than that of the same name column in student and some values may not fit, you could simply use a filter (a WHERE clause) to limit the inserted rows to those where the actual length of SNAME does not exceed a certain value:

INSERT INTO STIDENT_A (SID, SNAME, SUB, MARKS)
SELECT SID, SNAME, SUB, MARKS
WHERE LEN(SNAME) <= @maxlength
;

您可以查詢元數據以確定所需列的最大長度.有多種方式，一種是使用sys.columns系統目錄:

You could query the metadata to determine the maximum length of the required column. There are various ways, one is to use the sys.columns system catalog:

DECLARE @maxlength int;
SELECT @maxlength = max_length
FROM sys.columns
WHERE object_id = OBJECT_ID('STIDENT_A')
  AND name = 'SNAME'
;

要確定哪些行無法插入:

To determine which rows could not be inserted:

INSERT INTO STIDENT_A (SID, SNAME, SUB, MARKS)
SELECT SID, SNAME, SUB, MARKS
WHERE LEN(SNAME) > @maxlength
;

這篇關于即使在循環中發生錯誤，如何繼續游標循環的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！