How do I replace / update multiple rows based the possibility that any of the columns in those rows-CodePudding

I have a table called Customers which contains a number of columns such as FirstName, LastName, DateFileOpened, OrderedInLastMonth, etc... There are more than 20 columns per row, and there are around 500 rows.

Each hour, I have a mechanism that scrapes another source of this data for updated customer records, and puts them into a temporary table, which then need to be copied into my main Customers table. However, any or all of the columns in any or all of the new rows could differ to the existing ones in Customers.

At present, and to avoid creating nearly-duplicate records, my pretty crappy code does eg a delete from [Customers] where CoOrigin = 'England', before then importing the new ones to take their place. However, I have other queries that need to run at around the same time as this, and often this gets in the way, resulting in those other queries returning 0 data because the customer records that may be returned are missing, thanks to the delete command.

Once again, I'm aware this is terrible coding, but I'm still quite new. I've looked at the update / replace statements but they seem to need to specify which columns in each row need updating, but it could be any of the 20 . I'm aware that this would achieve the task, but it seems like more bad code. I'm also unsure how to reference the temporary table that the new records are imported into, before they are then copied to the main Customers table (and the temporary one dropped).

Any help or pointers you can give me would be very much appreciated. Thanks.

CodePudding user response：

You are dealing with a microscopic amount of data, so you can use a "big hammer" approach to refresh every customer without impacting other processes.

begin;
lock table customer in exclusive mode;
delete from customer;
insert into customer select from temp_customer;
commit;

Processes that need access to the customer table will just block while the update completes (a couple of seconds tops) then continue unaffected.

CodePudding user response：

try SQL Trigger maybe it can help you for this problem

https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/