CODEWITHSUNDEEP
mysqlmariadb
Leonardo
Leonardo

Reputation: 1363

Insert into same table multiple times

I need a script to insert into a relationship table. This relationship table had one ID column from table A, for example. We added a new column, an ID from table B.

Is there a way to INSERT INTO A SELECT... for each row in B, the A id and the B id?

I'm trying with a stored procedure (which is not working), but if you have any other suggestion, feel free.

DELIMITER $$

CREATE PROCEDURE fixNewIDColumn()
BEGIN 

    DECLARE a_id INT;
    DECLARE b_id INT;
    DECLARE finished_a BOOL DEFAULT FALSE;
    DECLARE finished_b BOOL DEFAULT FALSE;
    DECLARE a_cursor CURSOR FOR SELECT id FROM a;
    DECLARE b_cursor CURSOR FOR SELECT id FROM b;

    OPEN a_cursor;
    BEGIN
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished_a = TRUE;
        a_loop: LOOP

            FETCH a_cursor INTO a_id;

            IF finished_a THEN
                LEAVE a_loop;
            END IF;

            OPEN b_cursor;
            BEGIN
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished_b = TRUE;

                b_loop: LOOP

                    FETCH b_cursor INTO b_id;

                    IF finished_b THEN
                        SET finished_a = FALSE;
                        LEAVE b_loop;
                    END IF;

                    INSERT INTO a VALUES (a_id, b_id); 

                END LOOP b_loop;
                CLOSE b_cursor;
            END;

        END LOOP a_loop;
        CLOSE a_cursor;
    END;
END

Upvotes: 0

Views: 782

Answers (1)

Eric
Eric

Reputation: 3257

Try this

INSERT INTO A(id)
SELECT id
FROM B

Try to use CROSS JOIN

INSERT INTO A(a_id, b_id)
SELECT a.a_id, b.b_id
FROM TableA a
CROSS JOIN TableB b

Upvotes: 1

Related Questions