Synchronize current database records with new data to be inserted

Question

I need to synchronize the data in my database table with the new data from the SQL query, where I need to delete the current records that are not in the new data, and insert new records that are not in the current data. I was able to do this in java using this pseudo code:

// 1) get all data in database and store it in list (currentList) // 2) get new data obtained through sql query and store it in list (newList) // 3) sync both list for(entry : currentList) { if(newList.contains(entry)) finalList.add(entry) } for(entry : newList) { if(!finalList.contains(entry)) finalList.add(entry) } // 4) delete all data from DB // 5) insert finalList data to DB 

This works great, however, I think it will have a performance problem when working with a large dataset, because I delete everything and re-insert the whole list instead of just inserting new records and deleting records not found in the new data .

Can you suggest a better way to do this? Is it possible to create an SQL query that can provide data synchronization?

Answer 1

Take a look at MERGE .

The design allows you to specify the conditions under which either update existing records or add new ones.

It basically looks like this:

 MERGE INTO target_table USING source_table ON (some condition) WHEN MATCHED ( UPDATE some_update_statement ) WHEN NOT MATCHED ( INSERT some_insert_statement ) 

Answer 2

You can also perform these operations in a stored procedure so as not to increase DB traffic unnecessarily.

However, this will not work for a huge number of records (for example, millions of records) or if each record is very large. In this case, try using the MERGE operations, as Ryan pointed out.

Consider the following PL / SQL pseudo-code (you can change it depending on your use case):

 PROCEDURE replace_with_list (newList) AS BEGIN /* Delete all the entries that are not present in the newList (eg by ID) */ /* Insert all the entries that present in newList and not present in your table */ END;