sql server programming: [quoted text, click to view]

There are three problems. One is in the logic of the WHERE clause.
When you test for "not equal" you can't just AND the two tests and get
the result you want. You would need

NOT (FinalTable.Field2 = TempTable.Field2
AND FinalTable.Field3 = TempTable.Field3)

I suggest you spend a few minutes with a few sample values and a truth
table to understand the difference. Or read up on boolean logic.

A second and larger problem is that the approach is that there is no
instance of FinalTable to really test against. If it were an UPDATE
you could use the reference in the UPDATE command, but an INSERT does
not reference existing rows in the target table. FinalTable has to
appear in a FROM clause somewhere in the SELECT.

Finally, what you need is a NOT EXISTS test in one form or another,
not the sort-of-join used. One way would be a LEFT OUTER JOIN with a
NULL test, but I prefer a NOT EXISTS test:

INSERT INTO FinalTable (Field2, Field3, Field 4)
SELECT Field2, Field3, Field4
FROM TempTable as A
WHERE NOT EXISTS
(SELECT * FROM FinalTable as B
WHERE A.Field2 = B.Field2
AND A.Field3 = B.Field3)

Roy Harvey

I have two tables: one that is a temp table with a single day's data,
one that contains all the records for all days. The design of these
tables is identical, other than the table names. I want to use DTS to
insert any records from the temp table into the final table if they
don't already exists (based on a two-field match).

For example, I try to to do this:

INSERT INTO FinalTable (Field2, Field3, Field 4)
SELECT Field2, Field3, Field4
[quoted text, click to view]
WHERE (FinalTable.Field2 <> TempTable.Field2)
AND (FinalTable.Field3 <> TempTable.Field3)

For the above example, assume "Field1" [not shown] is a unique Primry
Key/Auto Increment.

In other words, if there is a record in the Final table that has the
same Field2 & Field3 data, don't copy [insert] it. I can't seem to
get the syntax correct - it either errors out when I try to save it,
or it doesn't insert any records (despite the fact that the Final
table is empty).

I realize that there are probably some performance issues with the
above, as I might be trying to do a record-by-record comparission. If
someone has a better way to do this, I would greatly appreciate any
suggestions.

Thank you!

Using the method you provided, I was able to get the desired result.
I will try to follow some of the guidelines you have outlined.

Thank you very much for your help.