SQL Server stored procedure - skip if exists, else insert

Question

I have trouble to create stored procedure to do skip for existed data and insert for not existed data.

Here is the current code:

ALTER PROCEDURE [dbo].[PrezentaImport2]
    @CodAngajat INT,
    @Data DATE,
    @CodTipOra VARCHAR(50),
    @R1DAL NUMERIC(10,1),
    @R1ALL NUMERIC(10,1),
    @R1TOT NUMERIC(10,1),
    @IdUtilizatorAdaugare INT
AS
    INSERT INTO Prezente (IdAngajat, IdTipPostDeLucru, IdPostDeLucru, IdDepartament, 
                          IdEchipa, IdLinie, Data, IdTipOra, R1DAL, R1ALL, R1TOT, 
                          IdUtilizatorAdaugare, DataAdaugare)
        SELECT 
            Angajati.Id, Angajati.IdTipPostDeLucru, Angajati.IdPostDeLucru, Angajati.IdDepartament,
            Angajati.IdEchipa, Angajati.IdLinie, @Data, TipuriOre.Id, @R1DAL, @R1ALL, @R1TOT,
            @IdUtilizatorAdaugare, GETDATE()
        FROM 
            Angajati
        INNER JOIN 
            TipuriOre ON TipuriOre.CodTipOra = @CodTipOra 
                      AND Angajati.CodAngajat = @CodAngajat

Thank you.

do you have any real joining condition? the way you wrote may bring about duplicates, or if it is good, you can join on `Prezente` again to specify which is missing from the table. Or either `trigger` or `Merge` may help — LONG, May 16 '18 at 15:04
Use SQL merge and refer this link - https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server/48546006#48546006 — Abdul Azeez, May 16 '18 at 15:08
The typical way to find items that *don't* exist is to `LEFT JOIN` with the target table and insert only the rows where the target ID (or any non-nullable field) is null — Panagiotis Kanavos, May 16 '18 at 16:30

score 0 · Accepted Answer · answered May 16 '18 at 16:30

Your code looks like SQL Server. A typical method uses merge. It looks something like this:

ALTER PROCEDURE [dbo].[PrezentaImport2] (
    @CodAngajat INT,
    @Data DATE,
    @CodTipOra VARCHAR(50),
    @R1DAL NUMERIC(10,1),
    @R1ALL NUMERIC(10,1),
    @R1TOT NUMERIC(10,1),
    @IdUtilizatorAdaugare INT
AS
BEGIN
    MERGE Prezente as target
        USING (SELECT . . .
               FROM Angajati a INNER JOIN
                    TipuriOre t
                    ON t.CodTipOra = @CodTipOra AND a.CodAngajat = @CodAngajat
              )
        ON target.? - source.?
    WHEN MATCHED THEN
        UPDATE SET IdAngajat = source.IdAngajat,
                   IdTipPostDeLucru = source.IdTipPostDeLucru,
                   . . .
    WHEN NOT MATCHED THEN
        INSERT (IdAngajat, IdTipPostDeLucru, IdPostDeLucru, IdDepartament, IdEchipa, IdLinie, Data, IdTipOra, R1DAL, R1ALL, R1TOT, IdUtilizatorAdaugare, DataAdaugare)
            VALUES (source.Id, source.IdTipPostDeLucru, source.IdPostDeLucru, source.IdDepartament, source.IdEchipa, source.IdLinie, @Data, source.Id, @R1DAL, @R1ALL, @R1TOT, @IdUtilizatorAdaugare, GETDATE()
    END;
END;

You will need to fill in the ? with the columns that define a duplicate, and the . . . with the rest of the assignment statements that you want.

SQL Server stored procedure - skip if exists, else insert

1 Answers1