DECLARE @Name NVARCHAR(50),@NRIC NVARCHAR(15),@PersonID INT
DECLARE CUR_Test CURSOR FOR
SELECT [Name],NRIC,PersonID FROM tblCtcPersonTest1
OPEN CUR_Test
FETCH NEXT FROM CUR_Test INTO @Name,@NRIC,@PersonID;
WHILE @@FETCH_STATUS =0
BEGIN
IF CHARINDEX('*',@NRIC)>0
BEGIN
INSERT INTO tblCtcPersonTest([Name],[PersonID],NRIC)VALUES(@Name,@PersonID,@NRIC)
END
ELSE
BEGIN
UPDATE tblCtcPersonTest1 SET Remarks='cant insert' WHERE PersonID=@PersonID
GOTO FORNEXT;
END
UPDATE tblCtcPersonTest1 SET Flag='success' WHERE PersonID=@PersonID
FORNEXT:
FETCH NEXT FROM CUR_Test INTO @Name,@NRIC,@PersonID;
END
CLOSE CUR_Test;
DEALLOCATE CUR_Test