6eCeyiw5

1Getting ALL last_inserted_ids [closed]
2START TRANSACTION;  
3INSERT INTO dbNEW.entity (commonName, surname)  
4SELECT namefirst, namelast  
5FROM dbOLD.user;  
6SET @key = LAST_INSERT_ID();  
7INSERT INTO dbNEW.user (userID, entityID, other)  
8SELECT user_id, @key, other  
9FROM dbOLD.user;  
10COMMIT;
11	
12dbOLD.user
13 +-------------+---------------------+------+-----+------------+----------------+
14 | Field       | Type                | Null | Key | Default    | Extra          |
15 +-------------+---------------------+------+-----+------------+----------------+
16 | user_id     | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
17 | namefirst   | varchar(20)         | NO   |     |            |                |
18 | namelast    | varchar(20)         | NO   |     |            |                |
19 | other       | varchar(10)         | NO   |     |            |                |
20 +-------------+---------------------+------+-----+------------+----------------+
21
22
23 dbNEW.user
24 +-------------+---------------------+------+-----+------------+----------------+
25 | Field       | Type                | Null | Key | Default    | Extra          |
26 +-------------+---------------------+------+-----+------------+----------------+
27 | userID      | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
28 | entityID    | int(10) unsigned    | NO   | MUL | 0          |                |
29 | other       | varchar(10)         | NO   |     |            |                |
30 +-------------+---------------------+------+-----+------------+----------------+
31
32
33 dbNEW.entity
34 +--------------+---------------------+------+-----+------------+----------------+
35 | Field        | Type                | Null | Key | Default    | Extra          |
36 +--------------+---------------------+------+-----+------------+----------------+
37 | entityID     | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
38 | commonName   | varchar(20)         | NO   |     |            |                |
39 | surname      | varchar(20)         | NO   |     |            |                |
40 +--------------+---------------------+------+-----+------------+----------------+
41	
42INSERT INTO dbNEW.`user` (userID, entityID, other)  
43SELECT user_id, entityID, other
44FROM
45(
46    SELECT user_id, @key + @rn entityID, other, @rn := @rn + 1
47    FROM (select @rn:=0) x, dbOLD.`user`
48    order by user_id
49) y;
50	
51DELIMITER $$
52DROP PROCEDURE IF EXISTS MigrateUsers $$
53CREATE PROCEDURE MigrateUsers ()
54BEGIN
55  DECLARE done INT DEFAULT 0;
56  DECLARE user_id INT;
57  DECLARE namefirst VARCHAR(20);
58  DECLARE namelast VARCHAR(20);
59  DECLARE other VARCHAR(10);
60  DECLARE lid INT;
61  /*Cursor looping over old users*/
62  DECLARE cur CURSOR FOR
63    SELECT user_id, namefirst, namelast, other
64    FROM dbOLD.user;
65
66  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
67
68  OPEN cur;
69  START TRANSACTION; 
70  read_loop: LOOP
71    FETCH cur INTO user_id, namefirst, namelast, other;
72    IF done THEN
73      LEAVE read_loop;
74    END IF;
75    /*Insert entity part*/
76    INSERT INTO dbNEW.entity (commonName, surname)
77      VALUES (namefirst, namelast);
78    SET lid = LAST_INSERT_ID();
79    /*Insert user part*/
80    INSERT INTO dbNEW.user (userID, entityID, other)  
81    VALUES (user_id, lid, other);
82
83  END LOOP;
84  COMMIT;
85  CLOSE cur;
86END$$
87DELIMITER ;
88	
89CREATE TABLE dbNEW.tempuser LIKE dbOLD.user;
90
91ALTER TABLE dbNEW.tempuser ENGINE=BLACKHOLE
92
93USE dbNEW
94
95DELIMITER $$
96
97CREATE TRIGGER tempuser_afterinsert AFTER INSERT on tempuser FOR EACH ROW<BR>
98BEGIN<BR>
99DECLARE maxid INT;<BR>
100INSERT INTO entity (commonName,surname) VALUES (NEW.namefirst,NEW.namelast);<BR>
101SELECT MAX(entityID) INTO maxid FROM entity;<BR>
102INSERT INTO user (userid,entityID,other) VALUES (NEW.user_id,maxid,NEW.other);<BR>
103END $$<BR>
104DELIMITER ; <BR>
105
106INSERT INTO dbNEW.tempuser (user_id,namefirst,namelast,other) SELECT user_id,namefirst,namelast,other FROM dbOLD.user;
107
108DROP TABLE dbNEW.tempuser;