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
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 ;
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