Logging missed insert records 1

[sogc]

Background
I have created a simple DTS Package to insert records into a table. Upon inserting the records from a tab delimited file I am picking up another field from an existing table via a join I created in the DTS Execute SQL Task object that is performing my insert.

Using SQL 2000

Problem
I have records in my tab delimited file for which there is no matching record in the joined table. The result is that the DTS Package executes with no errors and only insert records that can find a match in the joined table. However, I get no error log input indicating the records from the tab delimited file that did not get inserted.

Question
How can I log records that do not get inserted?

 

[JohnDTampaBay]

As you said, the records from your input file are being eliminated by the INNER JOIN you are using for the INSERT. If you just want to know which records from the input file didn't get inserted, you will need another task to INSERT those records into another table. Something like this will produce the rows from the input file that do not have a corresponding row in the join table. Good luck!

INSERT InputErrors
SELECT i.*
FROM InputTable i
   LEFT OUTER JOIN JoinTable j
       ON i.key = j.key
WHERE j.key IS NULL

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)