How do I compare values between two tables and send email if it has new value?

Question

My system imports every day CSV files from several directories into a table called TB_ARQUIVO. In this file, it contains the name of the Hotel (HOTEL_NOME column) and four types of Channels... SEGMENT, ORIGIN, COMMUNICATION and VEHICLE, outside other columns..

I have other tables where I have previously registered the information I use to score customers. That is, I have the Tb_hotel with all hotels registered that are compared with the file imported in the HOTEL_NOME column. As I have the following tables TB_SEGMENTO, TB_COMUNICACAO, TB_VEICULO and TB_ORIGEM.

Sometimes I get hotel names or channel names that are not registered in these tables. With that, I have to manually register and reimport the file generating a lot of work.

How do I generate a select that brings me all hotel names + all channels and compare with the tables already registered in order to identify new hotels and channels and send them by email ?

Everything in TB_ARQUIVO in the fields HOTEL_NOME, SEGMENT, COMMUNICATION, ORIGIN and VEHICLE and does not exist in TB_HOTEL, TB_SEGMENTO, TB_COMUNICACACAO, TB_VEICULO and TB_ORIGEM must be sent by e-mail.

Someone knows a simple way to do this ?

Answer 1

Recently I did something similar to compare data, and for the same used Except, to return only the new records on a list, see if it suits you:

/*Pego todas as descrições da minha lista(esta lista contem descrições novas e já cadastradas no BD)*/
var occurrencesDescription = model.CustomOccurrences.Select(o => o.Description).ToList();

/*Faço uma busca no BD usando as descrições(aqui ele só retornará as já cadastradas no BD)*/
var occurrences =repository.GetNonDeliveryReasonsListByDescriptionList(occurrencesDescription);

/*Uso o Except para me retornar apenas as que não foram localizadas na consulta acima*/
var newsOccurrences = model.CustomOccurrences.Select(o => o.Description).Except(occurrences.Select(o => o.Description)).ToList();

separate only the new you treat as you want, in my case I register new records.

Answer 2

If you want to not register double I suggest you create a Trigger for Insert to validate if the record already exists and block the Insert. But if you only want to send the mail at the time of the insert, you also create a Rigger put with the email sending code, which is a little more complicated nothing more...

if you use sql server here is a tutorial on how to do there just implement on Trigger https://technet.microsoft.com/pt-br/library/ms190437(v=sql.105). aspx