-1
CREATE DEFINER=`root`@`localhost` PROCEDURE `Migrate`()
BEGIN
    DECLARE new_id INT;
    START TRANSACTION;
        INSERT INTO new_customers(firstname, lastname)
        SELECT
            firstname,
            lastname
        FROM customers;

        SET new_id = (SELECT LAST_INSERT_ID());
        -- SELECT LAST_INSERT_ID() INTO new_id;

        INSERT INTO new_addresses(customer_id, address_line_1, address_line_2)
        SELECT
            new_id,
            address_line_1,
            address_line_2
        FROM addresses;
    COMMIT;
END

-------------------------------------
id  customer_id  
37  43  1 John Doe Street   Doeville
38  43  1 John Smith Way    Smithville
39  43  1 Jane Smith Avenue Janeville

Any ideas? Perhaps I need to FETCH a cursor and LOOP through it? I also am not convinced the addresses wwould always match up correctly?

This worked, thanks Akina!

CREATE DEFINER=`root`@`localhost` PROCEDURE `Migrate`()
BEGIN
    START TRANSACTION;
        INSERT INTO new_customers(firstname, lastname)
        SELECT
            firstname,
            lastname
        FROM customers;

        INSERT INTO new_addresses(customer_id, address_line_1, address_line_2)
        SELECT
            nc.id,
            a.address_line_1,
            a.address_line_2
        FROM addresses a, customers c, new_customers nc
        WHERE c.firstname = nc.firstname
        AND c.lastname = nc.lastname
        AND c.address_id = a.id;
    COMMIT;
END
  • Please explain the logic of this procedure. – Akina Nov 14 at 11:25
  • Why did you add a variable definition? it is not used anywhere... – Akina Nov 14 at 12:10
  • It was being used when I was trying to get the new Id, just hadn't removed it. – AlwaysLearning Nov 14 at 14:27
1

It seems you need:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Migrate`()
BEGIN
    START TRANSACTION;

    INSERT INTO new_customers(firstname, lastname)
    SELECT
        firstname,
        lastname
    FROM customers;

    INSERT INTO new_addresses(customer_id, address_line_1, address_line_2)
    SELECT
        nc.customer_id,
        a.address_line_1,
        a.address_line_2
    FROM addresses a, customers c, new_customers nc
    WHERE c.firstname=nc.firstname
      AND c.lastname=nc.lastname
      AND a.customer_id=c.id;

    COMMIT;
END
0

Don't bother to have a separate variable for the id:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Migrate`()
BEGIN
    START TRANSACTION;
        INSERT INTO new_customers(firstname, lastname)
        SELECT
            firstname,
            lastname
        FROM customers;

        INSERT INTO new_addresses(customer_id, address_line_1, address_line_2)
        SELECT
            LAST_INSERT_ID(),
            address_line_1,
            address_line_2
        FROM addresses;
    COMMIT;
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.