Help with Insert Query 1

[dpgirl]

I'm brain-dead and can't think of how to write the following insert query. Need help. Thanks!

I have 2 tables. Table A and Table B. The layouts of the 2 tables are different but for purposes of this exercise, I'm only concerned with 2 fields that both tables have in common: TFN and Campaign

Here's sample data for each table:

Table A--
TFN Campaign
1 X
2 Z

Table B--
TFN Campaign
1 X
1 Y
2 W
3 X

I want to insert all records from Table B into Table A that meet the following criteria: the TFN matches to an existing TFN value in Table A but the Campaign value corresponding to that TFN does not already exist in Table A.

Here's the desired end result for Table A:

TFN Campaign
1 X
2 Z
1 Y
2 W

Note: The record where TFN=3 is not inserted into Table A because TFN=3 does not already exist in Table A. The record where TFN=1 and Campaign=X is not inserted into Table A because that combination of values already exists in Table A (no duplicates allowed).

 

[Otto]

insert into TABLE_A (TFN, CAMPAIGN, other fields)
select TFN, CAMPAIGN, other fields from TABLE_B TB
where not exists (select TFN from TABLE_A
where TFN=TB.TFN and CAMPAIGN=TB.CAMPAIGN) and
exists (select TFN from TABLE_A where TFN=TB.TFN)