Stored procedure not inserting all the rows

92 views Asked by At

For a school project we need to make a web application. One of the features should be that you can import a .csv file and all the rows should get inserted into the right tables, except if (in this case) a song or artist already exists. The .csv file consists of 2000 rows which should always be inserted into a different table. The code runs, however only 137 rows are actually inserted. Here is my code:

BEGIN
SET NOCOUNT ON;

IF EXISTS(SELECT naam FROM Artiest WHERE naam = @artiest)
PRINT NULL;
ELSE
INSERT INTO Artiest(naam) VALUES (@artiest);
END
BEGIN
SET NOCOUNT ON;

IF EXISTS(SELECT titel FROM Song WHERE titel = @titel AND artiestid = (SELECT artiestid FROM Artiest WHERE naam = @artiest))
PRINT NULL;
ELSE
INSERT INTO Song(artiestid, titel, jaar) VALUES ((SELECT TOP(1) artiestid FROM Artiest WHERE naam = @artiest), @titel, @jaar);
END
BEGIN
SET NOCOUNT ON;

IF EXISTS (SELECT TOP(1) songid FROM Lijst WHERE songid = (SELECT TOP(1) songid FROM Song WHERE titel = @titel AND artiestid = (SELECT artiestid FROM Artiest WHERE naam = @artiest)))
AND EXISTS (SELECT TOP(1) top2000jaar FROM Lijst WHERE top2000jaar = @top2000jaar)
PRINT null;
ELSE
INSERT INTO Lijst (songid, top2000jaar, positie, uitzendDatum, van, tot) VALUES ((SELECT TOP(1) songid FROM Song WHERE titel = @titel AND artiestid = (SELECT artiestid FROM Artiest WHERE naam = @artiest)), @top2000jaar, @positie, @eindzenddatum, @van, @tot);
END

Hope someone can help me with this one, because I am a newb when it comes to SQL

1

There are 1 answers

0
classic_vmk On

I have simplified the sql to make it more readable,

BEGIN
    SET NOCOUNT ON;

    DECLARE @artiestID INT,
            @SongID INT

    SELECT @artiestID = artiestid FROM Artiest WHERE naam = @artiest

    IF @artiestID IS NULL
    BEGIN
        INSERT INTO Artiest(naam) VALUES (@artiest);
        SELECT @artiestID = @@IDENTITY
    END

    SELECT @SongID = songid FROM Song WHERE titel = @titel AND artiestid = @artiestID

    IF @SongID IS NULL
    BEGIN
        INSERT INTO Song(artiestid, titel, jaar) VALUES (@artiestID, @titel, @jaar);
        SELECT @SongID = @@IDENTITY
    END

    IF NOT EXISTS (SELECT 1 FROM Lijst WHERE songid = @SongID) OR NOT EXISTS (SELECT 1 top2000jaar FROM Lijst WHERE top2000jaar = @top2000jaar)
        INSERT INTO Lijst (songid, top2000jaar, positie, uitzendDatum, van, tot) VALUES (@SongID, @top2000jaar, @positie, @eindzenddatum, @van, @tot);
END

Verify your data for the reasong why the target table having less number of records.