MySQL How to INSERT INTO [temp table] FROM [Stored Procedure]
i know this is coming really late but since it took me ages to find a real solution i might as well share. I worked on an example that is below.
the tables created are:
CREATE TABLE BOOK(
B_ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(B_ID),
TITLE VARCHAR(100),
DESCRIPTION VARCHAR(30),
PRICE DOUBLE);
CREATE TABLE BOOK_COMMENT(
PRIMARY KEY(B_C_ID),
B_C_ID INT NOT NULL AUTO_INCREMENT,
REMARK VARCHAR(120),
B_ID INT,
FOREIGN KEY(B_ID) REFERENCES BOOK(B_ID));
CREATE TABLE AUTHOR(
A_ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(A_ID),
A_NAME CHAR(15),
B_ID INT,
FOREIGN KEY(B_ID) REFERENCES BOOK(B_ID));
- DELIMITER
CREATE PROCEDURE BOOK_IMPORTANT( _PRICE DOUBLE, _B_ID INT, A_NAME CHAR(15), _BD_ID INT)
BEGIN
INSERT INTO BOOK(PRICE)
VALUES(_PRICE);
SET _B_ID=LAST_INSERT_ID();
INSERT INTO BOOK_COMMENT(B_ID)
VALUES(_B_ID);
SET _BD_ID=LAST_INSERT_ID();
INSERT INTO AUTHOR(A_NAME,B_ID)
VALUES(A_NAME,_BD_ID);
END
then use the following to insert the values.
CALL BOOK_IMPORTANT('0.79',LAST_INSERT_ID(),'',LAST_INSERT_ID());
LAST_INSERT_ID() takes the last auto increment of the table and inserts it into the referencing column of the child table.
In the procedure parameters _B_ID and _BD_ID represent the B_ID since I need B_ID as a foreign key in both tables.
Sorry for the excess wording. All the other guys expect you to automatically know how to do it. Hope it helps
You cannot "SELECT INTO" with stored procedures.
Create the temporary table first and have your stored procedure to store the query result into the created temporary table using normal "INSERT INTO". The temporary table is visible as long as you drop it or until the connection is closed.
The problem is, Stored Procedures don't really return output directly. They can execute select statements inside the script, but have no return value.
MySQL calls stored procedures via CALL StoredProcedureName(); And you cannot direct that output to anything, as they don't return anything (unlike a function).
MySQL Call Command