How can I remove my company's email address from a data extension?

Question

How can I remove my company's email addresses from a data extension? and add then to a new data extension. If I'm supposed to use a SQL query, can you help me with that?

I want to remove a few records who are from my company so that I can see the number of people actually existing in the data extension.

Thanks!!

Answer 1

You can't remove specific records from a data extension using a Query Activity. Query Activities can only Overwrite, Update or Append records, not remove them. So, you'll need to take a different approach.

If you want to avoid SQL, create two filtered data extensions - one with a filter selecting only records where the email address field ends with your company's domain and the other containing only those records that do not end with your company's domain.

If you feel the need to use SQL, create two new data extensions from your existing data extension. Then create a Query Activity to populate each of your new Data Extensions:

SELECT  *
FROM    myDataExtension
WHERE   email LIKE ('%@mycompany.com')

And...

SELECT  *
FROM    myDataExtension
WHERE   email NOT LIKE ('%@mycompany.com')

Answer 2

You can also use a SSJS script activity to do this.

Retrieve the rows you need (up to 2500, beyond that you need WSProxy):

var birthdayDE = DataExtension.Init("birthdayDE");
var filter = {Property:"Age",SimpleOperator:"greaterThan",Value:20};
var data = birthdayDE.Rows.Retrieve(filter);

Add the rows to your new DE:

var arrContacts =  [
      {Email:"jdoe@example.com",FirstName:"John",LastName:"Doe"},
      {Email:"aruiz@example.com",FirstName:"Angel",LastName:"Ruiz"}
     ];

var birthdayDE = DataExtension.Init("birthdayDE");
birthdayDE.Rows.Add(arrContacts);

Delete the rows from the original DE:

var birthdayDE= DataExtension.Init("birthdayDE");
birthdayDE.Rows.Remove(["FirstName", "Age"], ["Angel", 24]);

The above are all via the Core library, so you will need to associate with Platform.Load("Core","1.1.1") at the top to utilize. You can also do to the add/delete via the Platform functions, but the platform lookup will not give the flexibility needed to get via domain. (only allows 'equals' simple operator)

If you are a bit of a developer you can also build this out via WSProxy which will be a bit more efficient.