2

I have 2 tables within SFMC both with SubscriberKey as the only field, which is the primary Key.

There are SubscriberKeys that exist in both tables.

Test1 has 1,700,000 records and Test 2 has 400,000 records, of which exist in Test1. I am trying to use EXCEPT to pull out all 400k from Test1 and push into a new DE, Test3.

I seem to remember using EXCEPT, which has worked for me before, but cannot seem to get it to work this time.

The query I am using is:

select * from [Test 1]

EXCEPT

select * from [Test 2]

I've tried to keep it as simple as possible, however running the above query gives me the exact amount of records as in the table Test1 (1,700,000), where i would expect (1,300,000).

1 Answer 1

9

You could do something like this:

select t1.subscriberkey 
from [test 1] t1
left join [test 2] t2 on (t2.subscriberkey = t1.subscriberkey)
where t2.subscriberkey is null

or

select t1.subscriberkey 
from [test 1] t1
where t1.subscriberkey not in ( 
  select t2.subscriberkey
  from [test 2] t2
)

or

select t1.subscriberkey 
from [test 1] t1
where not exists ( 
  select *
  from [test 2] t2
  where t2.subscriberkey = t1.subscriberkey
)
1
  • Brilliant, thanks mate - just what i was looking for! All work perfectly!
    – cantlon
    Jun 12, 2017 at 11:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .