Append data from one table to another table

I have a Destination table with 3 columns, ID, Name, Source. I have 10+ Source tables, each with multiple columns, but I only require the ID, Name, and the table name itself to be appended into Destination table. Do note the naming of the column names are different in each table, but the required ID and Name are of the same data type and are of sufficient length for the ID and Name fields.

I already have the query to add the data (see below), and I have no issues doing the first run to add the required data into the destination table, as I just need one query for each table, Here is one my code below

INSERT INTO dest_table
SELECT ID, Name, 'source_table' as Source
FROM source_table

The issue now is that I need to schedule this to run on a daily basis. I would like the source tables to append their new data into the destination table, and not add all records from each source table into the destination table.

Another condition to consider is that I will still need the data from the destination table to be intact. This means what ever records that were removed from the source tables , will not be removed from the destination table.

Thanks people!

Answer

You can exclude the old data by using a WHERE clause as shown below. I am assuming that the ID is unique in this table among all tables else you need to add another column in the destination table to identify where this ID is coming from

INSERT INTO dest_table 
SELECT ID, Name, 'source_table' as Source FROM source_table
WHERE NOT EXISTS (SELECT 1 FROM dest_table dt WHERE dt.id = source_table.id)

Another non-optimal approach would be to create a trigger on insert in the source tables and push the data to the destination table.