I have created a query activity that selects all fields from data extension DE1 and I set DE2 as target data extension. By running the query activity manually, records are copied from one data extension to another. Now I need to schedule this activity so I created an automation where I added this query activity. The automation runs and completes but the problem is that DE2 is not being updated.
For starters, there are 3 different kinds of ways the query can update the data that you can specify:
Update: Updates any existing data in the data extension and adds any new data (this requires at least one primary key)
Append: Adds the results of the query to the data extension after the existing records.
Overwrite: The system deletes the data in DE2 and puts in all new data from DE1.
Overwrites are certainly the easiest and most basic query types and they might be the right one to use in this case.
If that doesn't seem to be working there might be another reason: you've got a corrupt object. I've had this issue before myself where I've sworn everything about my query looks correct. What I've had to do is delete objects, re-create them, and then test each one until I've found the corrupted object. Start with the automation, then the query, then possibly the data extensions. Usually it's the automation but I've also seen issues with the query object as well. It's certainly one of the things I hate about Marketing Cloud and wouldn't be surprised if this was the case for you.