how to insert into table but only if the record doesnt exist

Question

I've got a table that stores the following:

JobID
ValidationItemID
CreatedBy

I want to be able to insert into this table (a predefined template) but only add rows that dont exist. What I mean by dont exist is the combination of JobID and ValidationItemID make the row unique. My procedure passes in a JobID, but I cannot pass in a validation item ID as I pull this column as part of the template...

Something to this effect:

CREATE PROCEDURE insTemplate 
    @JobID varchar(50),
    @Login varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO
            ValidationItemSignOff
                (
                    JobID,  
                    ValidationItemID,
                    CreatedBy
                )

        SELECT 
        DISTINCT
            @JobID,
            vi.ValidationItemID,
            @Login
        FROM
            RunOffAnswer roa
        INNER JOIN
            Method m ON m.MethodID = roa.MethodID
        INNER JOIN
            RunOffValidationItem vi ON vi.ValidationItemID = m.ValidationItemID
        WHERE
            vi.Inactive=0
            AND NOT EXISTS(SELECT * FROM ValidationItemSignOff WHERE JobID=@JobID AND vi.ValidationItemID ???
END
GO

I dont know how to phrase the where condition so that it doesn't reinsert the same JobID and ValidationItemID. Lets say I have inside the table:

Job      ValidationItem
Job A          1
Job A          2
Job A          5

And I have a template with the following:

ValidationItem
1
2
3
4
5
6

When I run my stored procedure it should only insert values 3,4,6 from the template table, for the job id... So I need help with my where condition.

I think my issue is I cannot use NOT EXISTS, maybe I need to join back to this ValidationItemSignOff table itself on JobID and ValidationItemID where ValidationItemID is NULL, maybe like this:

CREATE PROCEDURE insTemplate 
    @JobID varchar(50),
    @Login varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO
            ValidationItemSignOff
                (
                    JobID,  
                    ValidationItemID,
                    CreatedBy
                )

        SELECT 
        DISTINCT
            @JobID,
            vi.ValidationItemID,
            @Login
        FROM
            RunOffAnswer roa
        INNER JOIN
            Method m ON m.MethodID = roa.MethodID
        INNER JOIN
            RunOffValidationItem vi ON vi.ValidationItemID = m.ValidationItemID
        RIGHT OUTER JOIN
            ValidationItemSignOff viso ON viso.JobID = @JobID
                  AND viso.ValidationItemID = vi.ValidationItemID
        WHERE
            vi.Inactive=0
            AND viso.ValidationItemID IS NULL
END
GO

Got it I think

Replacing the right join with this:

LEFT JOIN
        ValidationItemSignOff viso
        ON  viso.JobID = @JobID
        AND viso.ValidationItemID = vi.ValidationItemID

Answer 1

I got it with this:

ALTER PROCEDURE insSignOffTemplate 
    @JobID varchar(50),
    @Login varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO
            ValidationItemSignOff
                (
                    JobID,  
                    ValidationItemID,
                    CreatedBy
                )

        SELECT 
        DISTINCT
            @JobID,
            vi.ValidationItemID,
            @Login
        FROM
            RunOffAnswer roa
        INNER JOIN
            Method m
        ON
            m.MethodID = roa.MethodID
        INNER JOIN
            RunOffValidationItem vi
        ON
            vi.ValidationItemID = m.ValidationItemID
        LEFT JOIN
        ValidationItemSignOff viso
        ON  viso.JobID = @JobID     AND viso.ValidationItemID = vi.ValidationItemID
        WHERE
            vi.Inactive=0
            AND viso.ValidationItemID IS NULL
END
GO

Answer 2

If you're on 2008 or above...

MERGE INTO ValidationItemSignOff As Target
USING (SELECT DISTINCT @JobID, vi.ValidationItemID, @Login
         FROM RunOffAnswer roa INNER JOIN 
              Method m
           ON m.MethodID = roa.MethodID INNER JOIN
              RunOffValidationItem vi
           ON vi.ValidationItemID = m.ValidationItemID
        WHERE vi.Inactive = 0) As Source (JobID, ValidationItemID, Login)
   ON Target.JobID = Source.JobID 
  AND Target.ValidationItemID = Source.ValidationItemID
 WHEN NOT MATCHED BY TARGET THEN
      INSERT (JobID, ValidationItemID, CreatedBy)
      VALUES (Source.JobID, Source.ValidationItemID, Source.Login);

Disclaimer: I may have not got the syntax spot on here.