There are 3 tables:

NewData; OldDara; DifData.

The NewData and OldData have the same set of columns:

 Email; Имя; Фамилия; Телефон; Должность.

The DifData table has the following columns:

 Email; ИмяИзминившегосяАтрибута(имя столбца); СтароеЗначениеАтрибута; НовоеЗначениеАтрибута.

Task:

Every day, the NewData table is cleared of data and fresh data about employees is entered into it (in fact, there are much more columns than in the example). There is a column with a unique Email value.

Next, you need to compare 2 tables NewData and OldData on the differing values ​​of the columns in each field, and record these differences in the third table.

How can this be implemented?

  • Cron as an option - mix
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

3 answers 3

Thank you very much, especially kld, your code helped a lot, further optimized it: First I got the name of the columns of the source table into an array and then based on the array I substituted them into your code.

 for ($i= 0; $i -ne $res.Count; $i++) { $q_dif_data +=" SELECT now(),UserPrincipalName,'$($res[$i]['field'])',users_new_data.$($res[$i]['field']),users_old_data.$($res[$i]['field']) FROM users_new_data INNER JOIN users_old_data USING(UserPrincipalName) WHERE users_old_data.$($res[$i]['field'])<>users_new_data.$($res[$i]['field']) UNION" } #____ Сравнили значения в таблицах с новыми данными и старыми данными ##### Вносим различающиеся данные в таблицу "test" $q_dif_data = "INSERT INTO test(DateOfEvent,UserPrincipalName,Parameter_name,New_value,Old_value) " + $q_dif_data $q_dif_data = $q_dif_data.TrimEnd("UNION") $sql.CommandText = $q_dif_data $sql.ExecuteNonQuery()

    Try this

     INSERT INTO (Email, ИмяИзминившегосяАтрибута(имя столбца), СтароеЗначениеАтрибута, НовоеЗначениеАтрибута) SELECT Email, colonka, imyaOld, imyaNew FROM ( SELECT Email, 'Имя' colonka, o.Имя imyaOld, n.Имя imyaNew FROM NewData n WHERE Email NOT IN ( SELECT Email FROM OldData ) o UNION SELECT Email, 'Имя', Имя, p.Имя, q.Имя FROM OldData p WHERE Email NOT IN ( SELECT Email FROM NewData ) q ) w

      [SQL Fiddle] [1] http://sqlfiddle.com/#!9/ba330/1/0

       CREATE TABLE newdata ( email varchar(100) NOT NULL, name varchar(100) DEFAULT NULL, surname varchar(100) DEFAULT NULL, phone varchar(100) Default NULL); INSERT INTO newdata values ('a@a.ru','Ivan','Petrov','13-33'), ('b@a.ru','Petr','Petrov','13-35'), ('c@a.ru','Sidr','Sidorov','13-33'); CREATE TABLE olddata( email varchar(100) NOT NULL, name varchar(100) DEFAULT NULL, surname varchar(100) DEFAULT NULL, phone varchar(100) Default NULL); INSERT INTO olddata VALUES ('a@a.ru','Petr','Petrov','13-33'), ('b@a.ru','Petr','Sidorov','13-35'), ('c@a.ru','Sidr','Sidorov','13-33'); CREATE TABLE difdata( email varchar(100) NOT NULL, colname varchar(100) DEFAULT NULL, olddata varchar(100) DEFAULT NULL, newdata varchar(100) Default NULL); INSERT INTO difdata (email,colname,olddata,newdata) SELECT email,'name',olddata.name,newdata.name FROM newdata INNER JOIN olddata USING(email) WHERE olddata.name<>newdata.name UNION SELECT email,'surname',olddata.surname,newdata.surname FROM newdata INNER JOIN olddata USING(email) WHERE olddata.surname<>newdata.surname UNION SELECT email,'phone',olddata.phone,newdata.phone FROM newdata INNER JOIN olddata USING(email) WHERE olddata.phone<>newdata.phone;

      How many fields to check for change, so much and UNION.