It's possible to skip duplicate keys (i.e. MERGE) in a BULK INSERT in MySQL? I have a INSERT...SELECT statement that I want to run several times from TABLE1 to TABLE2 as the data in TABLE1 will change overtime.

I don't expect the past data from select to change overtime, the select will just return more new data, and the same old data of previous queries.

But I cannot insert old data again because of the unique key constraint on the primary keys.

What I would like to achieve is a kind of INSERT ...ON DUPLICATE KEY UPDATE... but for a INSERT...SELECT.
Skipping the duplicate keys, will be enough, but I'm not sure how to do it
There is no MERGE statement in MySQL. You can do this quite easily as follows (all DDL and DML shown at bottom of post and also in the fiddle here):

I created and populated two tables foo_1 and foo_2 with identical data and identical PRIMARY KEYs, the fields (foo_x, foo_y);

I then added a record to table foo_1.

Then I performed the query:

INSERT INTO foo_2 
SELECT * FROM foo_1 WHERE (foo_x, foo_y) -- foo_x, foo_y are the PK!
NOT IN (SELECT foo_x, foo_y FROM foo_2);


And then SELECT * FROM foo_2 and the new record from table foo_1 had been copied over to table foo_2. This is a simple and effective method of doing what you want as far as I can see. Doing this with a single field PRIMARY KEY is even easier!

=============== DDL and DML ============

table foo_1:

CREATE TABLE foo_1
(
  foo_x INTEGER NOT NULL,
  foo_y INTEGER NOT NULL,
  foo_2 INTEGER NOT NULL,
  foo_3 VARCHAR (25) NOT NULL,
  PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_1 
VALUES 
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');


and table foo_2:

CREATE TABLE foo_2
(
  foo_x INTEGER NOT NULL,
  foo_y INTEGER NOT NULL,
  foo_2 INTEGER NOT NULL,
  foo_3 VARCHAR (25) NOT NULL,
  PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_2
VALUES 
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');


Insert new record into table foo_1:

INSERT INTO foo_1 VALUES (1, 5, 24, 'foo_3_val_5');


And then:

INSERT INTO foo_2 
SELECT * FROM foo_1 WHERE (foo_x, foo_y)
NOT IN (SELECT foo_x, foo_y FROM foo_2);


And then check that table foo_2 now contains the single new record from table foo_1:

SELECT * FROM foo_2;


Result:

foo_x   foo_y   foo_2   foo_3
1       1           2   foo_3_val_1
1       2           7   foo_3_val_2
1       3           8   foo_3_val_3
1       4           9   foo_3_val_4
1       5          24   foo_3_val_5