Transfer rows into a new table, forcing to generate unique primary keys till its insertable Lets say we have the following scenario...
+-----------------+-------------+---------------+
| Table           | PK          | Additional    |
+-----------------+-------------+---------------+
| playerinventory | id (int)    | amount, level |
+-----------------+-------------+---------------+
| newinventory    | id (BIGINT) | amount, level |
+-----------------+-------------+---------------+

We are in the middle of a architectural change of our game-database.
&quot;amount, level&quot; should get extracted from &quot;playerinventory&quot; and get inserted into &quot;newinventory&quot;.
Limitations

&quot;newinventory&quot; has no auto-increment pk, therefore we need to
generate a unique long for each... we also can not add
&quot;auto-increment&quot; to the table
&quot;newinventory&quot; could possible
already contain those identities, so we need to check if the
generated id already exists and if so repeat the process for the
affected set of extracted columns till it was inserted
successfully.
We CANT simply insert each row from
&quot;playerinventory&quot; into &quot;newinventory&quot; with its current id... that's
simply not possible (if I would explain why this post would be much,
much longer), that's why we NEED to generate a new unique
ID

In nearly any programming language this process would look like this
for row in playerInventory
   id = generate new ID
   while(id in newInventory) id = generate new ID
   insert into newInventory( id, row.amount, row.level )
      

How can we achieve this in MySQL/SQL ?
The highest ID in &quot;newinventory&quot; is already 9223372036854775806 and therefore we cant increase anymore... we need to generate and test till it fits.
use a BEFORE INSERTTRIGGGER to generate UNIQUE numbers.
With SET @NEW_ID = 1; you define the first new value
Schema (MySQL v8.0)
CREATE table playerinventory ( id int PRIMARY KEY ,    amount INT , level int);
CREATE table newinventory ( id bigint PRIMARY KEY ,    amount INT , level int);

INSERT INTO playerinventory VALUEs (5,1,1),(6,2,2),(7,3,3),(8,4,4);

DELIMITER $$
CREATE TRIGGER before_newinventory_insert
BEFORE INSERT
ON newinventory FOR EACH ROW
BEGIN
    
    SELECT MAX(id) + 1
    INTO @NEW_ID
    FROM newinventory;
    IF @NEW_ID IS NULL THEN
        SET @NEW_ID = 1;
    END IF;
    set NEW.id = @NEw_ID;
END $$

DELIMITER ;

INSERT INTO newinventory SELECT * FROM playerinventory;


Query #1
SELECT * FROM newinventory;

| id  | amount | level |
| --- | ------ | ----- |
| 1   | 1      | 1     |
| 2   | 2      | 2     |
| 3   | 3      | 3     |
| 4   | 4      | 4     |


View on DB Fiddle