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?
Apparently, you've got a RETURN
statement immediately after ROLLBACK TRANSACTION
. Have you tried just removing it?
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.
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.
UPDATE addressing the comments
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
;
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
;
Try This:-
DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag <=5)
BEGIN
begin Try
if @intFlag = 3
begin
SET @intFlag = @intFlag + 'A'
End
Else
begin
PRINT @intFlag
end
End Try
Begin Catch
--print 'Error'
-- continue
End catch
SET @intFlag = @intFlag + 1
END
GO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With