0

Hi I created a migration script that accepts json file which has thousands of records. I dont have control on the url endpoint of the api that returns a json data. I successfully imported the records on my own database but I need to run a cron task everyday to see if there's a record added/deleted or updated then apply it on my database.

Question is, with these large amount of records, is it better to delete the records on my database then reimport the data from the api endpoint (json) or Do a loop/query/loop/condition to figure out what's got updated/deleted and added then apply it on the database?

user3912200
  • 1
  • What would be the least disruptive would be to import into a background table like `your_table_temp`, then when it's done rename `your_table_temp` to `your_table` and vice-versa. The rename takes a very small fraction of a second. – Jared Farrish Aug 05 '14 at 22:32
  • thank you, unfortunately the tbl that I need to insert the records to is a global table for pages, like tbl_pages which has an existing pages, Which I also dont have control, I just used 1 field as a modifier to identify if it came from an api endpoint. – user3912200 Aug 05 '14 at 22:35
  • Is there a `last_updated` field or anything like that you can use in the json data? Because that would be the way to go. – Wesley Murch Aug 05 '14 at 22:35
  • You could hold the last file and manipulate each so that you could run a diff and only update what appears to have changed. Or do an MD5 column and use that to search on. There's really a lot of ways of doing this; whichever one you choose has to do with whatever your specific circumstances are. – Jared Farrish Aug 05 '14 at 22:46
  • thank you guys, so it's ok to insert a query(like find query) inside a loop with thousands of records? – user3912200 Aug 05 '14 at 22:55
  • @user3912200 have you heard of bulk insert in sql, that might be a solution – meda Aug 05 '14 at 23:18
  • As @meda indicates, you could [`INSERT OR UPDATE`](http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql). – Jared Farrish Aug 05 '14 at 23:22

0 Answers0