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?

RichardTheKiwi · Accepted Answer · 2011-02-20 03:49:13Z

5

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.

answered Feb 20, 2011 at 3:49

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

The Scrum Meister Over a year ago

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

RichardTheKiwi Over a year ago

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.

edl Over a year ago

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?

RichardTheKiwi Over a year ago

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

The Scrum Meister · Accepted Answer · 2011-02-20 00:41:12Z

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

Collectives™ on Stack Overflow

Mysql mulitple row insert-select statement with last_insert_id()

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related