What would be most elegant way to replicate data contents periodically into a MySQL table?

I need to parse contents from an API route and insert into a MySQL database. The aim of parsing contents is to replicate the data from the API to my database table.

I need to check API response every day (at a specified time) using scheduled cron jobs and should fetch and insert all available records into the database.There could be changes in the response every time and it should be reflected as provided in the API.

There isn't any fields which could be considered for making a unique ID as well.

I can't search and sort as its not an updating task alone, hence rather than 'updating' of records, we should doing 'replication' of data from the API. We need to consider,

• deletion of removed records from the API response,

• updating modified record fields and

• Also the insertion of new records.

Consider an example:

On day 1,

Let the records available with the API be A1, A2, A3, A4

There could be duplicate entries among this records .

We will be parsing the API response and insert ever record available as it is. The script will fetch records (A1, A2, A3, A4) and insert it to our table.

Our table will hence contain records :( A1, A2, A3, A4)

Consider on the next day,

Let the records available with API be A1,A2,A3,A5,A6 -

Possible cases:

-
There could updated field values for each records A1, A2, and A3 than
day 1

-
A4 is removed from the API response now

-
New records A5,A6 are added now.

In this case our aim is to update the table such that, it should only have records A1, A2, A3, A5, A6 with their updated values.

The data is expected to be structured.There are specific fields from API,which are to be parsed and fetched.We can't estimate how often the changes would be made,and its asked to make update every day.

The response field values might change but not the structure.The fields for which the values should be fetched will remain same,only the change would affect the field values.

Currently there are about 2000 records ,which could increa

If you don't need to retain the data from the previous day when you update the data, you could do this without downtime:

Day 1:

• insert data from API into Table1

Day 2+:

• create a new table with the same structure as Table1

create NewTable like Table1;

• insert API data into NewTable (note: app is still using Table1 at this point)

• switch out the tables in one statement

rename Table1 to OldTable, NewTable to Table1;

• drop OldTable now if you want to, or keep some archives

StackExchange Database Administrators Q#84570, answer score: 4