3

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).

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
)
  • Brilliant, thanks mate - just what i was looking for! All work perfectly! – cantlon Jun 12 '17 at 11:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.