February 11, 2016 at 2:59 pm
Hello,
I have created a cursor which should find multiple rows, but only INSERTs one record into my table. Can someone help me understand what is wrong in my cursor?
Here is my code:
DECLARE
@EIDvarchar(1000),
@First_Namevarchar(100),
@Last_Namevarchar(100),
@Titlevarchar(50)
DECLARE db_cursor CURSOR FOR
SELECT EID, First_Name, Last_Name, Title
FROM EMP_REF
WHERE (NOT EXISTS(SELECT EID FROM dbo.EMP)) AND (Processed = 0)
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @EID, @First_Name, @Last_Name, @Title
WHILE @@FETCH_STATUS = 0
BEGIN
-- STEP 1
INSERT INTO EMP (EID, First_Name, Last_Name, Title)
SELECT @EID, @First_Name, @Last_Name, @Title
-- STEP 2
INSERT INTO EMP_History (EID, First_Name, Last_Name, Title)
SELECT @EID, @First_Name, @Last_Name, @Title
-- STEP 3
UPDATE EMP_REF SET Processed=1 WHERE EID=@EID
FETCH NEXT FROM db_cursor
INTO @EID, @First_Name, @Last_Name, @Title
END
CLOSE db_cursor
DEALLOCATE db_cursor
February 11, 2016 at 5:01 pm
Your cursor is dynamic. Which means that it is not only very slow, but also re-evaluated for every fetch.
After the first pass through the loop, a row exists in EMP - because you just added it. This makes the EXISTS false for every row, so no row now qualifies for the next fetch,
Why do you use a cursor at all?
If you want help replacing this with setbased code, then please post table definitions (as CREATE TABLE statements, including constraints and indexes), a limited set of illustrative test data (as INSERT statements), and expected results plus an explanation of the logic.
February 11, 2016 at 5:07 pm
You may want to use a WHERE CURRENT OF clause with this. Please check this out: https://technet.microsoft.com/en-us/library/ms188713%28v=sql.105%29.aspx
Please see if that helps in what you are trying to achieve.
-Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply