I'm currently wanting to copy a subset of subscribers from one publication list into another. The flow of the automation is planned to be something like this:
- SQL Activity to populate a data extension with subscribers that need to be copied
- Data extract activity to export csv of these contacts to safe house
- File transfer activity to move file from safehouse to location on FTP
- File import activity to load contents of CSV into new list.
The plan is currently failing at the first hurdle and the query I've written in the SQL activity isn't populating the data extension with any subscribers. This is the query I'm using.
SELECT gm.EmailAddress,gm.SubscriberKey,gm.Status FROM _ListSubscribers gm LEFT JOIN _ListSubscribers cr ON gm.SubscriberKey = cr.SubscriberKey WHERE cr.SubscriberKey IS NULL AND cr.ListID = 37464 AND gm.ListID = 35439
Is it possible to join two publication lists together in this way in a SQL query? If so why might the query not be returning any results?
The list with ID 37464 is currently empty and the list with ID 35439 has 9709 subscribers in it.I'm expecting that the data extension will be populated with all the 9709 subscribers on the left side of the join as none of them are present in the publication list on the right side.