I have two SQL tables, table A and table B.

Initially I copied all the rows from table A to table B, lets say on date D. Now I want to drop table A. Before dropping I want to copy all the new record entries(made after date D) to table B. While copying I need to check if any of the originally copied row has updated, and if it did, replace it with the updated data. I have created timestamp and updated timestamp field in both the tables.

In short, I need to perform both update and insert to copy new data from one table to another. Can't drop table B because it has additional data.

CodePudding user response：

1. Add a flag (bit column) such as archived by default 0.

When you copy rows from table A to table B, execute:

UPDATE `table A` SET `archived` = 1

So you can find rows that never copied to table B:

SELECT * FROM `table A` WHERE `archived` = 0;
-- OR:
INSERT INTO `table C`
SELECT * FROM `table A` WHERE `archived` = 0;

I don't know what do you want to updated rows of table A?

You want to update them to table B or copy them to table C? If you need, when you modify a row, you can change archived to 0 again.

CodePudding user response：

changes base on your comment:

1. Update previous rows on table B that has changes on table A

UPDATE `table A` a
INNER JOIN `table B` b ON a.id = b.id
SET
  b.X = a.X,
  b.Y = a.Y,
  /* and more columns */
  b.`updated` = a.`updated`
WHERE (b.`updated` IS NULL AND a.`updated` IS NOT NULL) OR b.`updated` <> a.`updated`

2. Insert new rows on table A that not found on Table B

INSERT INTO `table B`
SELECT * FROM `table A` WHERE `archived` = 0;

3. Mark inserted rows (step 2) as archived in table A

UPDATE `table A` SET `archived` = 1;

Tables:

on table B nothing will set automatically:

Not id by AUTO_INCREMENT and not created and updated by current_timestamp()

CREATE TABLE `table A` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `X` INT(11) NOT NULL DEFAULT '0',
    `Y` INT(11) NOT NULL DEFAULT '0',
    `created` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    `updated` TIMESTAMP NULL DEFAULT NULL ON UPDATE current_timestamp(),
    `archived` BIT(1) NOT NULL DEFAULT b'0',
    PRIMARY KEY (`id`) USING BTREE
);

CREATE TABLE `table B` (
    `id` INT(10) UNSIGNED NOT NULL,
    `X` INT(11) NOT NULL DEFAULT '0',
    `Y` INT(11) NOT NULL DEFAULT '0',
    `created` TIMESTAMP NULL DEFAULT NULL,
    `updated` TIMESTAMP NULL DEFAULT NULL,
    `archived` BIT(1) NOT NULL,
    PRIMARY KEY (`id`) USING BTREE
);