Using NOT EXISTS in an INSERT procedure

[Colin Spalding]

I am using the following code to insert records into a destination table
that has a three column primary key i.e. (PupilID, TermID &
SubjectGroup). The source table records all the pupils in a school with
(amongst other things) a column (about 50) for each subject the pupil
might potentially sit. In these columns are recorded the study group
that they belong to for those subjects. The destination table holds a
record per pupil per subject per term, against which the teacher will
ultimately record the pupils performance.

The code as shown runs perfectly until the operator tries to insert a
selection of records that include some that already exist. What I would
like it to do is, record those, which do not exist and discard the
remainder. However, whenever a single duplicate occurs SQL rejects the
whole batch. I know that my solution will probably involve using the
‘NOT EXISTS’ expression, but try as I might I cannot get it to work. To
further complicate things, the code is being run from within VBA using
the RunSQL command.

The variables ‘strFieldName’, ‘strGroup’ & ‘strTerm are declared at the
start of the procedure and originate from options selected on an Access
form.

INSERT INTO dbo.yInterimReportData (PupilID, LastName, FirstName,
TermID, SubjectGroup) SELECT PupilID, LastName, FirstName," & "'" &
strTerm & "'" & "," & "'" & strGroup & "'" & "FROM dbo.Pupils WHERE (" &
strFieldName & " = " & "'" & strGroup & "')

Any Ideas?
Regards
Colin

*** Sent via Developersdex http://www.developersdex.com ***

[Simon Hayes]

Probably something like this:

insert into dbo.InterimReportData (PupilID, LastName, FirstName,
TermID, SubjectGroup)
select PupilID, LastName, FirstName, @TermID, @SubjectGroup
from dbo.Pupils p
where SubjectGroup = @SubjectGroup
and not exists (
select *
from dbo.InterimReportData i
where p.KeyColumn = i.KeyColumn
)

The best solution would probably be to put this into a stored proc, and
simply pass @TermID and @SubjectGroup as parameters, which is easier
and more secure than building the whole query dynamically.

Simon