即使在循环中发生错误，如何继续游标循环
我想将一个表的行插入到另一个表中.为此，我有以下程序.
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
;
相关文章