即使在循环中发生错误,如何继续游标循环

How to continue cursor loop even error occured in the loop(即使在循环中发生错误,如何继续游标循环)
本文介绍了即使在循环中发生错误,如何继续游标循环的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我想将一个表的行插入到另一个表中.为此,我有以下程序.

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_ASNAME的最大长度小于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
;

这篇关于即使在循环中发生错误,如何继续游标循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Creating table with T-SQL - can#39;t see created tables in Object explorer(使用 T-SQL 创建表 - 在对象资源管理器中看不到创建的表)
How to check if VARCHAR strings are (not) hexadecimal?(如何检查 VARCHAR 字符串是否为(非)十六进制?)
Arithmetic overflow error converting IDENTITY to data type int(将 IDENTITY 转换为数据类型 int 的算术溢出错误)
Where clause if there are multiple of the same ID(如果有多个相同的 ID,Where 子句)
Azure SQL: Invalid Object Name using Powershell#39;s quot;Invoke-sqlcmdquot; on Adventureworks(Azure SQL:使用 Powershell 的“Invoke-sqlcmd的无效对象名称在 Adventureworks 上)
How to Convert Table Data into xml format using sql with multiple sub nodes(如何使用具有多个子节点的sql将表数据转换为xml格式)