Mysql mulitple row insert-select statement with last_insert_id()

Question

Ok. So the short of it is, I was trying to do an INSERT SELECT such as:

START TRANSACTION;  
INSERT INTO dbNEW.entity (commonName, surname)  
SELECT namefirst, namelast  
FROM dbOLD.user;  
SET @key = LAST_INSERT_ID();  
INSERT INTO dbNEW.user (userID, entityID, other)  
SELECT user_id, @key, other  
FROM dbOLD.user;  
COMMIT;

Of course @key does not return each subsequent LAST_INSERT_ID() from each insert but the ID from only the last insert.

Basically, I'm splitting an old USER Table into an ENTITY and USER like:

 dbOLD.user
 +-------------+---------------------+------+-----+------------+----------------+
 | Field       | Type                | Null | Key | Default    | Extra          |
 +-------------+---------------------+------+-----+------------+----------------+
 | user_id     | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
 | namefirst   | varchar(20)         | NO   |     |            |                |
 | namelast    | varchar(20)         | NO   |     |            |                |
 | other       | varchar(10)         | NO   |     |            |                |
 +-------------+---------------------+------+-----+------------+----------------+


 dbNEW.user
 +-------------+---------------------+------+-----+------------+----------------+
 | Field       | Type                | Null | Key | Default    | Extra          |
 +-------------+---------------------+------+-----+------------+----------------+
 | userID      | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
 | entityID    | int(10) unsigned    | NO   | MUL | 0          |                |
 | other       | varchar(10)         | NO   |     |            |                |
 +-------------+---------------------+------+-----+------------+----------------+


 dbNEW.entity
 +--------------+---------------------+------+-----+------------+----------------+
 | Field        | Type                | Null | Key | Default    | Extra          |
 +--------------+---------------------+------+-----+------------+----------------+
 | entityID     | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
 | commonName   | varchar(20)         | NO   |     |            |                |
 | surname      | varchar(20)         | NO   |     |            |                |
 +--------------+---------------------+------+-----+------------+----------------+

Why would I want to do this? Basically, I have a "STORE" entity which is going to have fields common to "USERS" such as address and phone number. So any "ENTITY" might have none to multiple addresses (shipping, billing, mailing) and none to multiple phone numbers (fax, main, billing, cell, home) There may be other ways to accomplish this, but this is the solution I ended up with.

The STOREs and USERS from the old db need to keep their old PKs and gain an additional ENTITY fk. How can I do this without making a dump and manually editing it?

Disappoint

Answer 1

For the last query, use this

INSERT INTO dbNEW.`user` (userID, entityID, other)  
SELECT user_id, entityID, other
FROM
(
    SELECT user_id, @key + @rn entityID, other, @rn := @rn + 1
    FROM (select @rn:=0) x, dbOLD.`user`
    order by user_id
) y;

The LAST_INSERT_ID() in MySQL is the FIRST id created in a batch, unlike SCOPE_IDENTITY() in SQL Server which is the LAST id. Since it is the first, we increment each row using the variable @rn, starting at addition=0 for the first row.

Piliferous

What if another session inserted a row in between, will this still work? – Vitellus

Another session cannot insert a row in between - the insert is atomic, it will grab a range of increment ids - at least I've never seen it break. – Piliferous

This worked beautifully, at least for the few rows I checked for consistency. My only concern is, it seems a bit hacky. Would you recommend this method to update a production environment? – Disappoint

@Disappoint MySQL side effect variables are in production environment all over the world - what's your specific concern? – Piliferous

Answer 2

This case may call for a cursor based solution, where you loop over the old users, and do the 2 individual inserts. This won't do bulk inserts, but it will be better then updating the rows manually.

DELIMITER $$
DROP PROCEDURE IF EXISTS MigrateUsers $$
CREATE PROCEDURE MigrateUsers ()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE user_id INT;
  DECLARE namefirst VARCHAR(20);
  DECLARE namelast VARCHAR(20);
  DECLARE other VARCHAR(10);
  DECLARE lid INT;
  /*Cursor looping over old users*/
  DECLARE cur CURSOR FOR
    SELECT user_id, namefirst, namelast, other
    FROM dbOLD.user;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  START TRANSACTION; 
  read_loop: LOOP
    FETCH cur INTO user_id, namefirst, namelast, other;
    IF done THEN
      LEAVE read_loop;
    END IF;
    /*Insert entity part*/
    INSERT INTO dbNEW.entity (commonName, surname)
      VALUES (namefirst, namelast);
    SET lid = LAST_INSERT_ID();
    /*Insert user part*/
    INSERT INTO dbNEW.user (userID, entityID, other)  
    VALUES (user_id, lid, other);

  END LOOP;
  COMMIT;
  CLOSE cur;
END$$
DELIMITER ;

I suggest you read the docs on Procedures and Cursors

Vitellus