Table sync and copy into other table

I have two tables. Table A and Table B. They are identical. Ever 10 min i need to check if there any changs happend (New and updated) to Table A and copy into Table B. And also enter in Table C if i see a differance and new.

I also need to log if there any new records in Table A to table B and Table C

Iam planning to开发者_运维百科 do join and compare the records. If i do that i might miss the new records. Is there any better way to do this kind of sync. It has to be done in SQL i can not use any other tools like SSIS.

---

Here's what I came up with in making some simple tables in SQL:

# create some sample tables and data
DROP TABLE alpha;
DROP TABLE beta;
DROP TABLE charlie;

CREATE TABLE `alpha` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `data` VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

CREATE TABLE `beta` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `data` VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

CREATE TABLE `charlie` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `data` VARCHAR(32) DEFAULT NULL,
  `type` VARCHAR(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

INSERT INTO alpha (data) VALUES ("a"), ("b"), ("c"), ("d"), ("e");
INSERT INTO beta (data) VALUES ("a"), ("b"), ("c");

---

# note new records of A, log in C
INSERT INTO charlie (data, type)
    (SELECT data, "NEW"
    FROM alpha
    WHERE id NOT IN
        (SELECT id
        FROM beta));

# insert new records of A into B
INSERT INTO beta (data)
    (SELECT data
    FROM alpha
    WHERE id NOT IN
        (SELECT id
        FROM beta));

# make a change in alpha only
UPDATE alpha
SET data = "x"
WHERE data = "c";

# note changed records of A, log in C
INSERT INTO charlie (data, type)
    (SELECT alpha.data, "CHANGE"
    FROM alpha, beta
    WHERE alpha.data != beta.data
    AND alpha.id = beta.id);

# update changed records of A in B
UPDATE beta, alpha
SET beta.data = alpha.data
WHERE alpha.data != beta.data
AND alpha.id = beta.id;

You would of course have to expand this for the type of data, number of fields, etc. but this is a basic concept if it helps.

---

It's a pity that you can't use SSIS (not allowed?) because it's built for this kind of thing. Anyway, using pure SQL you should be able to something like the following: if your tables have got a created/updated timestamp column, then you could query Table B for the highest one and get all records from table A with timestamps higher than that one. If there's no timestamp to use, hopefully there's a PK like an int that can be used in the same way.

Hope that helps? Valentino.

---

I would try using a trigger or transactional replication.

---

Hopefully you have a good unique key that is used in the tables. To get new records you can do the following:

SELECT * FROM  tableA
WHERE NOT EXISTS( SELECT * FROM tableB WHERE pkey.tableA = pkey.TableB)