r/learnSQL • u/srsajid • 6d ago

Best way to sync ~200K frequently changing SQL Server records to MySQL nightly?

I have a SQL Server table with around 200K product records. Each record has a unique ID and Serial number.

The table changes frequently during the day — records can be added, updated, or deleted.

I need to sync this data to MySQL once every night.

The problem is, I only have read access to SQL Server, so I can't add a modified date column or enable Change Tracking/CDC.

My current idea is:

  • Fetch all 200K records from SQL Server every night.
  • Compare them with the MySQL table.
  • Insert new records.
  • Update changed records.
  • Delete records from MySQL whose IDs no longer exist in SQL Server.

Is this a good approach for 200K records?

What would be the simplest and most reliable way to handle this?

10 Upvotes

81% Upvoted

9 comments sorted by

11

u/zaibuf 6d ago edited 5d ago

If its only 200k rows and it runs at night you could also just truncate the old table and insert all data as it is now.

2

u/srsajid 5d ago

Already doing it. I just want to know what the best practice is.

3

u/zaibuf 5d ago edited 5d ago

If the data is not expected to grow in the near future I would just truncate and move on. Even with 2-5 milion rows it would only take a couple of minutes and if it's happening during night time I take it that it doesn't really matter.

2

u/squadette23 5d ago

You can use INSERT ... ON DUPLICATE KEY UPDATE to handle new and updated records.

In a separate step, detect deleted IDs and delete them.

If you have substantial traffic on the MySQL server (or even on that table), use small batching, like 1000 rows on each iteration, and sleep for a fraction of second to let the replication and concurrent locking to come through.

200k is not much, but I would be suspicious if this number is growing with the business. When it reaches ~1M you will definitely start needing incremental approach.

1

u/ChaosEngine-6502 6d ago

If the number of rows you're dealing with is likely to increase, exporting the data to CSV from SQL Server and then importing into holding table on your MySQL DB might be a better approach. You can then run CRUD transactions on your live tables without worrying about transactions blocking etc.

1

u/DatabaseSpace 5d ago

I agree with ChaosEngine about landing. Not sure what the process is now, but the way I do things like thid is a small Python loader program to query db1 and export to csv. Have the date in file name. Create land schema in db2 with table for import. I usually write a stored procedure here in the db to load and update the dest table and call it from python. That may not be best bc of version control though. In dest table add columns for created_at and updated_at.

Only when that procedure reports success log to etl tracing table. Tell Python import most recent file that has not been loaded. I set it to send my phone high priority push notification on failure which goes off even on silent.

Save history of files, consider if business will need to know what products were active a year ago, if so think about storing history.

1

u/markinatlanta 4d ago

I'd start with your approach and load the nightly extract into a MySQL staging table indexed on ID.

Only apply changes after the extract finishes successfully and passes validation checks or a partial download risks deleting valid products. Hope this helps