How do i insert in an insert command with mysql?

Question

I have a simple insert select which insert _TABLE_B_ data in _TABLE_A_ new row

INSERT INTO _TABLE_A_(_USERNAME_,_ID_) 
SELECT  _USERNAME_,_ID_ 
FROM _TABLE_B_

I want to insert a row in a table named _TABLE_C_ each time i insert a row in _TABLE_A_ and add the current inserted _TABLE_C_ id in _TABLE_A_.

i'll try to explain it in an other way :

INSERT INTO _TABLE_A_(_USERNAME_,_ID_,_FOREIGN_ID_) 
SELECT  B._USERNAME_,B._ID_,C._FOREIGN_ID_
FROM _TABLE_B_ AS B 
LEFT JOIN _TABLE_C_ AS C
#Insert a row in _TABLE_C_ to retrieve _FOREIGN_ID_...

I'm searching for a single minimal query which have the INSERT SELECT statement like mine because insert select can loop and i have to loop.

FYI : I'm in a stored procedure. I also use prepared statements with dynamic data, and cursors is not suitable for dynamic data select...

Danny T. · Accepted Answer · 2010-01-21 14:24:39Z

1

I would do all the INSERTs in _TABLE_C_ first and then join it in the INSERT _TABLE_A_ to get the appropriate foreign keys.

If that is not possible, I would use a cursor.

Cursor on _TABLE_B_ & Fetch

INSERT _TABLE_C_

INSERT _TABLE_A_ with Foreign_Id = SCOPE_IDENTITY()

Fetch next

answered Jan 21, 2010 at 14:24

Danny T.

1,14010 silver badges23 bronze badges

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

6 Comments

belaz Over a year ago

ok no matter if it works, show me ;D remember i have to do the inserts at the same time to avoid corrupted data

Danny T. Over a year ago

What are your columns in _TABLE_C?

belaz Over a year ago

i see, but i cant use cursor, i use an insert select with a where with dynamic data passed to the stored procedure, the cursor will throw an error if i use my where with my variables, i read the mysql documentation about cursor, and i test it also.

belaz Over a year ago

TABLE_C = ID, CREATED_AT, UPDATED_AT

Danny T. Over a year ago

Ok, and if you declare variables in your stored procedure to store a copy of your parameters and use the variables in your select-where, does it work? If not, je suis désolé, but unfortunately I don't have an answer for you.

|

belaz · Accepted Answer · 2010-01-21 17:52:36Z

I found a solution.

create a temporary table and add dynamic select statement which retrieve the primary keys (id)
declare a cursor and select this temporary table id ( variables doesn't work but temporary tables do )
execute statement to create temporary table
open the cursor and iterate the inserts

EXAMPLE

  BEGIN


    DECLARE isDone INT DEFAULT 0;
    DECLARE fetchedmemberWhoWillReceiveMailId int;

    DECLARE cur1 CURSOR FOR SELECT id FROM memberWhoWillReceiveMail;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET isDone = 1;
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    SET @sexe     = VAR_sexe; 
    SET @event    = VAR_eventId;
    SET @subject = VAR_subject;
    SET @body     = VAR_body; 
    SET @to        = VAR_to; 
    SET @from  = VAR_from;
    SET @region    = VAR_region;
    SET @departement      = VAR_departement; 
    SET @age        = VAR_age;

    SET @baseSqlStatement =' CREATE TEMPORARY TABLE memberWhoWillReceiveMail SELECT e.id FROM TABLE_A as e LEFT JOIN TABLE_B AS a on a.member_id = e.id';

    SET @whereSqlStatement= 'WHERE e.is_visible = 1 AND e.member_group_id IN (10,11) ';

    IF (@region!='') THEN  
    SET @whereSqlStatement=  CONCAT(@whereSqlStatement,' AND region=',@region);
    END IF;

    IF (@event !=null ) THEN 
    SET @whereSqlStatement=  CONCAT(@whereSqlStatement,' AND m.event_id !=' ,@eventId); 
    END IF;

    IF (@sexe!=null ) THEN
    SET @whereSqlStatement=  CONCAT(@whereSqlStatement,' AND e.sexe=',@sexe); 
    END IF;

    SET @baseSqlStatement = CONCAT(@baseSqlStatement,@whereSqlStatement);

    START TRANSACTION;
    PREPARE stmt1 FROM @baseSqlStatement;
    EXECUTE stmt1; 
    DEALLOCATE PREPARE stmt1;

    OPEN cur1; 
    FETCH cur1 INTO fetchedmemberWhoWillReceiveMailId; 
    WHILE NOT isDone DO
        INSERT INTO conversation(created_at,updated_at)VALUES(now(),now());
        INSERT INTO message(created_at,updated_at,from, to, uniqueID)   VALUES(now(),now(),@from,fetchedmemberWhoWillReceiveMailId,LAST_INSERT_ID() );
        FETCH cur1 INTO fetchedmemberWhoWillReceiveMailId; END WHILE; CLOSE cur1;

    COMMIT;
    DROP TEMPORARY TABLE IF EXISTS memberWhoWillReceiveMail;

    END

Collectives™ on Stack Overflow

How do i insert in an insert command with mysql?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related