Thanks for reading this. I'm trying to work out the best strategy to keep my customer table up to date.
I have a customer table plus 6 daily transaction tables that get new data every day. If there is a new customer, they might come in the transaction table so I need to find the customers in the transaction table that aren't in the customer table and add them. This seems right to me but I wanted to get input from others because it seems really inefficient with "select the customer that is not already in the primary table" when the customer table is so big. The data is loaded every day.
Here is my command:
insert into customer (customer, reptdate) (select distinct customer, getdate() as reptdate from T_TX_CUST where not customer in (select customer from customer ))
The thing is, as part of the transaction table processing, I go through the tables row wise in C#. I could do a select there to see if the customer existed and then add them if they don't. The programmer in me really wants to do it this way but using SQL would be much simpler but I'm afraid of the cost. Is using a set of SQL commands in a stored procedure faster than doing a select and then an insert on a record by record basis?
I would really appreciate any thoughts or advice.