script.sql

# insert to table2 where year is same with table2 using records ot table1
Insert INTO emails_message_body_2019 SELECT emails_message_body.* FROM emails_message_body
INNER JOIN emails on emails.id = emails_message_body.email_id
where Year(date_entered) = 2019
LIMIT 0, 100


# delete from table1 where email_id is already existing in table2
# this might be slow but its safe
DELETE FROM emails_message_body
WHERE
  EXISTS(SELECT 1 FROM emails_message_body_2019 WHERE emails_message_body_2019.email_id = emails_message_body.email_id)

z_script.php

<?php

/**
 * Example URL
 * http://192.168.35.10/extranet/index.php?module=Emails&action=MigrateEmailMessageBodyTable
 * http://192.168.35.10/extranet/index.php?module=Emails&action=MigrateEmailMessageBodyTable&delete=true&limit=1000
 * http://192.168.35.10/extranet/index.php?module=Emails&action=MigrateEmailMessageBodyTable&delete=true&limit=1000&year=2006
 */

// check if user is allowed to trigger this
global $current_user;
if (!in_array($current_user->id, ["68592759-ca9e-012e-8868-5cc8b4b31d49"])) {
	e("You are now allowed to trigger this");
}

// determine what year should be a table be created and data to be migrated
$year = $_REQUEST['year'] ? $_REQUEST['year'] : 2005;

// determine if how many records should be used in a single transaction
$limit = $_REQUEST['limit'] ? $_REQUEST['limit'] :  1;

// set to true if should delete from table1 where email_id is already existing in table2
$delete = $_REQUEST['delete'] === 'true' ? true :  false;

$table1 = 'emails_message_body';
$table2 = "emails_message_body_$year";
$log = '';

// create table2 if it does not exist
require_once('modules/Emails/Email.php');
$focus = new Email();
if (!$focus->table_exist($table2)) {
	$log .= e_echo("CREATING TABLE $table2");
	$createTableQuery = "
	CREATE TABLE `$table2` (
		`email_id` varchar(36) NOT NULL,
		`raw_message` longtext,
		`compression` int(11) NOT NULL DEFAULT '0',
		PRIMARY KEY (`email_id`),
		KEY `compression` (`compression`)
		) ENGINE=MyISAM DEFAULT CHARSET=latin1;
	";
	$db = new PearDatabase();
	$results = $db->query($createTableQuery, true);
	$log .= e_echo("CREATED TABLE $table2");
}

// start of insert
$countQuery = "
	SELECT COUNT(*) as total FROM $table2
		INNER JOIN
			emails on emails.id = $table2.email_id
		WHERE
			YEAR(emails.date_entered) = $year
	";
$results = $db->query($countQuery, true);
$row = $db->fetchByAssoc($results);
$count = $row['total'];
$log .= e_echo("Before insert in $table2 using limit $limit: Count $count");

// insert to table2 where year is same with table2 using records ot table1
$insertQuery = "
	REPLACE INTO $table2
		SELECT $table1.*  FROM $table1
		INNER JOIN
			emails on emails.id = $table1.email_id
		WHERE
			YEAR(emails.date_entered) = $year
		LIMIT 0, $limit
";
$db = new PearDatabase();
$results = $db->query($insertQuery, true);

$results = $db->query($countQuery, true);
$row = $db->fetchByAssoc($results);
$count = $row['total'];
$log .= e_echo("After insert in $table2 using limit $limit: Count $count");
// end of insert



if ($delete) {
	$countQuery = "
	SELECT COUNT(*) as total FROM $table1
		INNER JOIN
			emails on emails.id = $table1.email_id
		WHERE
			YEAR(emails.date_entered) = $year
	";
	$results = $db->query($countQuery, true);
	$row = $db->fetchByAssoc($results);
	$count = $row['total'];
	$log .= e_echo("Before delete in $table1: Remaining $count");

	// delete from table1 where email_id is already existing in table2
	// this might be slow but its safe
	$deleteQuery = "
		DELETE FROM $table1
		WHERE
  		EXISTS(
			SELECT 1 FROM $table2
			 WHERE $table2.email_id = $table1.email_id)
	";
	$results = $db->query($deleteQuery, true);

	$results = $db->query($countQuery, true);
	$row = $db->fetchByAssoc($results);
	$count = $row['total'];
	$log .= e_echo("After delete in $table1: Remaining $count");
}

e($log);