15 Maj 2008

Wypełnianie bazy danymi z pliku, nowe, zmiany, usuniete

drop database mojedane2;
create database mojedane2;
use mojedane2;

create table nowe (klucz char(8), line text);
create table usuniete (klucz char(8), line text);
create table zmiany (klucz char(8), line text);

create table mojedane_old (klucz char(8), line text);
create table mojedane_new (klucz char(8), line text);

LOAD DATA INFILE 'D:\\mojedane\\05MAY.TXT' INTO TABLE mojedane_old
LOAD DATA INFILE 'D:\\mojedane\\05Jul.txt' INTO TABLE mojedane_new

insert into nowe (line) select mojedane_new.line from mojedane_new LEFT JOIN mojedane_old ON mojedane_new.klucz = mojedane_old.klucz WHERE mojedane_old.klucz is NULL;
select mojedane_new.line into outfile 'd:\\mojedane\\nowe.txt' from mojedane_new LEFT JOIN mojedane_old ON mojedane_new.klucz = mojedane_old.klucz WHERE mojedane_old.klucz is NULL;
insert into usuniete (line) select mojedane_old.line from mojedane_old LEFT JOIN mojedane_new ON mojedane_old.klucz = mojedane_new.klucz WHERE mojedane_new.klucz is NULL;
select mojedane_old.line into outfile 'd:\\mojedane\\usuniete.txt' from mojedane_old LEFT JOIN mojedane_new ON mojedane_old.klucz = mojedane_new.klucz WHERE mojedane_new.klucz is NULL;
insert into zmiany (line) select new.line from mojedane_new AS new, mojedane_old AS old where new.klucz = old.klucz AND new.line <> old.line
select new.line into outfile 'd:\\mojedane\\zmiany.txt' from mojedane_new AS new, mojedane_old AS old where new.klucz = old.klucz AND new.line <> old.line