CODEWITHSUNDEEP
sqlsql-server
Anurag
Anurag

Reputation: 57

insert into select not exists sql server multiple columns primary key voilation

I have a table tblApply which have primary key(email_id, Job_id) which are foreign key of tblEmail(email) and tblJob(Job_id). I want to insert new values in tblApply but avoid those which are already there in a combination of email_id, Job_id. Below is my code. How should i do it. I am doing this inside a stored procedure.

insert into tblApply(email_Id, Job_Id, Apply_Date)
    select @emailId, tblJobsJob_Id, GetDate() from tblJobs
    where Job_Active = 1

Upvotes: 3

Views: 2930

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

To insert the non-existing records, you can use the condition check by LEFT JOIN with NULL check.

INSERT INTO tblApply (email_Id, Job_Id, Apply_Date)
SELECT @emailId, tblJobsJob_Id, GetDate() 
FROM tblJobs TJ
LEFT JOIN tblApply TA ON TA.email_id = @emailId AND TA.Job_id = TJ.tblJobsJob_Id 
WHERE TY.Job_Active = 1 AND TA.Job_id IS NULL

Upvotes: 1

Mocanu Adrian
Mocanu Adrian

Reputation: 46

Well from what I understand you wish to design a stored procedure for inserting rows that will avoid inserting duplicate unique key values.

If that's the case you could do:

insert into tblApply(email_Id, Job_Id, Apply_Date)
select @emailId, tblJobsJob_Id, GetDate() from tblJobs
where Job_Active = 1 AND 
      NOT EXISTS(SELECT 1 FROM tblApply 
                 WHERE email_Id = @emailId AND Job_Id = tblJobsJob_Id)

Upvotes: 2

Related Questions