How do I compare values between two tables and send email if I have a new value?

Navigation
0

My system imports CSV files from multiple directories every day into a table called TB_ARQUIVO. In this file, it contains the name of the Hotel (Column HOTEL_NOME) 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 TB_HOTEl with all the registered hotels that are compared with the imported file in the HOTEL_NAME column. So 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 this, I have to manually register and reimport the file generating a lot of work.

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

Everything that exists in TB_ARQUIVO in the fields HOTEL_NAME, SEGMENT, COMMUNICATION, ORIGIN and VEHICLE and does not exist in TB_HOTEL, TB_SEGMENTO, TB_COMUNICACAO, TB_VEICULO and TB_ORIGEM should be sent by email.

Does anyone know a simple way to do this?

    
asked by anonymous 27.09.2017 / 17:19

2 answers

1

Recently I did something similar to compare data, and for that I used Except , to return only the new records in a list, see if it caters to 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 ones you treat as you wish, in my case I register new registrations.

    
27.09.2017 / 17:52
0

If you do not want to register double I suggest you create a Trigger for the insert to validate if the record already exists and lock the insert. But if you just want to send the mail at the time of the insert, you also create a trigger with the email sending code, which is a bit more complicated and nothing more ...

If you use sql server here you have a tutorial on how to do it, just implement it in the trigger link

    
27.09.2017 / 22:52
Webservice delete error [closed] In this Laravel Validator, what is the correct code with variable?