Cursor for each row-CodePudding

I'm in need of help. While inserting new data I need to use cursors. For now, I'm managed to insert data by matching two tables and after the match is done, two values stored in variables are inserted into the third table.

The only issue is that every row has the same value which is wrong. Ultimately, I have no idea why is this happening, trying to include count, changing the list of cursors which thing should happen first, etc.

Declare @Ugovor_ID varchar(50), 
        @Zupanija_ID varchar(50),
        @Broj_ugovora varchar(50),
        @Naziv_Zup varchar(50)

DECLARE cc CURSOR FOR SELECT Ug_zup.[Broj ugovora], Ug_zup.Zupanija FROM Ug_zup

OPEN cc
FETCH NEXT FROM cc into @Broj_ugovora, @Naziv_Zup 

WHILE @@FETCH_STATUS=0
BEGIN
SET @Ugovor_ID   = (SELECT Ugovor_ID FROM Ugovor WHERE "Broj_ugovora" = @Broj_ugovora)
SET @Zupanija_ID = (SELECT Zupanija_ID FROM Zupanija_Sif WHERE Zupanija_naziv = @Naziv_Zup)


INSERT INTO Ugovor_Zupanija (Ugovor_ID, Zupanija_ID)
SELECT @Ugovor_ID,@Zupanija_ID

FETCH NEXT FROM cc into @Ugovor_ID, @Zupanija_ID 
END
CLOSE cc
DEALLOCATE cc
GO

THE RESULT IS LIKE THIS;

pic of the result

CodePudding user response：

Your issue is that at first you fetch into these two variables:

FETCH NEXT FROM cc into @Broj_ugovora, @Naziv_Zup

But then within your loop you fetch into different variables:

FETCH NEXT FROM cc into @Ugovor_ID, @Zupanija_ID

That means that @Broj_ugovora and @Naziv_Zup never change value. Since they never change, the result of this assignment also never changes:

SET @Ugovor_ID   = (SELECT Ugovor_ID FROM Ugovor WHERE "Broj_ugovora" = @Broj_ugovora)
SET @Zupanija_ID = (SELECT Zupanija_ID FROM Zupanija_Sif WHERE Zupanija_naziv = @Naziv_Zup)

Also never changes, so you insert the same values over and over again.

The quick fix is to just change the fetch inside the loop to match the initial fetch.

The proper fix is to rewrite as a set based query. I think the following is the equivalent of what you are doing:

INSERT INTO Ugovor_Zupanija (Ugovor_ID, Zupanija_ID)
SELECT  u.Ugovor_ID,
        sif.Zupanija_ID
FROM    Ug_zup AS zup
        LEFT JOIN Zupanija_Sif AS sif
            ON sif.Zupanija_naziv = zup.Zupanija
        LEFT JOIN Ugovor AS u
            ON u.Broj_ugovora = zup.[Broj ugovora];