Since you have very much data at hand, I suggest you merge your date and time columns first. Then you can use an index efficiently. If you don't, you will have to do something like
...WHERE CONCAT(date, ' ', time) = SELECT MAX(CONCAT(date, ' ', time)) ...
So, first do this for both tables.
ALTER TABLE tableA ADD COLUMN creation_date datetime; /*or whatever name, just make it meaningful and don't use keywords*/
UPDATE tableA SET creation_date = CONCAT(date, ' ', time);
ALTER TABLE tableA DROP COLUMN date, DROP COLUMN time;
CREATE INDEX idx_dt_tableA_creation ON tableA(creation_date);
Then you can insert both tables into your combine_table
(Note, left this for completeness, the second option is much better).
INSERT INTO combined_table
SELECT col1, col2, creation_date
FROM (
SELECT col1, col2, creation_date
FROM tableA
UNION ALL
SELECT col1, col2, creation_date
FROM tableB
) sq /*subquery_alias*/
WHERE creation_date = (SELECT MAX(creation_date) FROM (
SELECT col1, col2, creation_date
FROM tableA
UNION ALL
SELECT col1, col2, creation_date
FROM tableB
) another_sq
WHERE sq.col1 = another_sq.col1
)
;
Nonetheless, this will be a heavy operation, if you really have that much data.
Now that I think of it, there's a better way of doing it.
First insert tableA
INSERT INTO combined_table
SELECT * FROM tableA;
Then do an insert on duplicate key update
.
INSERT INTO combined_table c
SELECT * FROM tableB b
ON DUPLICATE KEY UPDATE
/*you can skip col1, since it's the identifying primary key here*/
col2 = IF(b.creation_date > c.creation_date, b.col2, c.col2),
creation_date = IF(b.creation_date > c.creation_date, b.creation_date, c.creation_date)
;
UNION ALL
andGROUP BY
the PK.