CODEWITHSUNDEEP
sqlt-sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

Find absent values in a junction table, rectify SQL Server 2008

I have two tables I'm dealing with, patientTbl which consists of a PK called patientID and a junction table called icdJxn. The query below is set up to find the values of patientID present in patientTbl NOT present in icdjxn. 

select patienttbl.patientid, icdjxn.patientID as [nulls?]
from patientTbl left outer join icdjxn on patientTbl.patientID = icdjxn.patientID
where (icdjxn.patientID is null)

I'm pretty sure that gives me the values from patientTbl.patientID that are not present in icdjxn.patientID. Is there a way to insert into this query so I won't run into any PK/FK issues?

Upvotes: 2

Views: 160

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

A merge would do the job if you are on SQL Server 2008 or later.

merge icdjxn as S
using patientTbl as T
on S.patientID = T.patientID
when not matched then
  insert (patientID) values (patientID);

Upvotes: 2

Related Questions