MySQL How to INSERT INTO [temp table] FROM [Stored Procedure]
This is very similar to question 653714, but for MySQL instead of SQL Server.
Basically, I have a complicated select that is the basis for several stored procedures. I would like to share the code across the stored procedures, however, I'm not sure how to do this. One way I could do this is by making the shared select a stored procedure and then calling that stored procedure from the other ones. I can't figure out how to work with the result set of the nested stored procedure. If I could put them in a temp table I could use the results effectively, but I can't figure out how to get them in a temp table. For example, this does not work:
CREATE TEMPORARY TABLE tmp EXEC nested_sp();
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).
Inserting Stored Procedure Results into Temporary Table, Learn how to insert the results of a stored procedure into a temporary table in SQL Server. In this data tutorial, we will outline options to INSERT results … To create a temporary table in a MySQL procedure, following is the syntax − CREATE PROCEDURE yourProcedureName() BEGIN CREATE TEMPORARY TABLE yourTemporaryTableName SELECT yourValue; END. Let us implement the above syntax to create a temporary table and insert some records in the table.
My first reaction was "That sounds like a view to me". Doesn't that abstract it enough so you can just add the variability into an SP per case?
Anything that adds a temp table that wouldn't otherwise be there is a very likely antipattern.
How to Insert Stored Procedure result into Temporary Table in SQL , In this article we will show you, How to write a SQL Query to Insert Stored Procedure result into Temporary Table in SQL Server with example. How to write a SQL Query to Insert Stored Procedure result into Temporary Table in SQL Server with example. For this SQL Interview Question, we are going to use the below shown data. TIP: I suggest you refer both the Stored Procedure and Temporary Table articles to get the basic idea. Data present in the Employee table inside our SQL Tutorial
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.
MySQL Temporary Table, Then, insert rows from the customers table into the temporary table credits : However, we can create a stored procedure that checks if a temporary table exists To get the output of your stored procedure into a table you use the INSERT statement. To do this, first create a table that will hold the output of the stored procedure. In that table create a column for every column that is outputted from your stored procedure. Next execute an INSERT statement where you stream the output of your stored
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));
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.
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
_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
MySQL Bugs: #60718: Stored Function: INSERT INTO TEMPORARY , Bug #60718, Stored Function: INSERT INTO TEMPORARY TABLE; Can't look SP_insert_temptable.sql mysql> CREATE TEMPORARY TABLE IF from a TEMP table twice with different aliases in a stored procedure are Inserting into a Table from another Table. The INSERT command can also be used to insert data into a table from another table. The basic syntax is as shown below. INSERT INTO table_1 SELECT * FROM table_2; Let's now look at a practical example, we will create a dummy table for movie categories for demonstration purposes.
Maybe it's a closed topic, but I would like to offer a solution based on the properties of MySQL temporary tables. First, the way to create the temporary table would not be to call the stored procedure "CREATE TEMPORARY TABLE tmp EXEC nested_sp ();". The query is to the temporary table of "infrastructure", (to name it somehow).
To achieve the desired result, it is necessary to create 2 stored procedures, the first stored procedure processes the data and fills the temporary "infrastructure" table, the second stored procedure, reads this table and continues with the process and finally "DROP" the "infrastructure" table
This is the first stored procedure:
CREATE DEFINER = 'root'@'localhost' PROCEDURE cajareal.priv_test() BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS tmp( column1 TEXT , column2 TEXT , column3 TEXT ); INSERT INTO tmp(column1, column2 , column3) VALUES(CURDATE(), CURRENT_DATE(), CURRENT_TIMESTAMP()); END
This is the second stored procedure:
CREATE DEFINER = 'root'@'localhost' PROCEDURE cajareal.priv_caller() BEGIN CALL priv_test; -- Read data of "infrastructure" table SELECT * FROM tmp; -- Do the business logic -- Delete the "infrastructure" table DROP TABLE tmp; END
I use this technique to analyze a string and convert it to the table
Create a temporary table in a MySQL procedure?, To create a temporary table in a MySQL procedure, following is the syntax Following is the query to create a stored procedure and a temporary table in it − INSERT INTO tmpDemoTable VALUES (300); -> END// Query OK, The INSERT INTO SELECT statement is very useful when you want to copy data from other tables to a table or to summary data from multiple tables into a table. MySQL INSERT INTO SELECT example. First, create a new table called suppliers : CREATE TABLE suppliers ( supplierNumber INT AUTO_INCREMENT, supplierName VARCHAR (50) NOT NULL,
How to Insert Results of Stored Procedure into a Temporary Table , Here is a simple script which demonstrates how we can insert the result of the stored procedure into a temporary table without pre-creating the @SureshKamrushi Can a MySQL prepared statement be use inside of this stored procedure to insert the row, or would that make everything break? I am playing around trying to do this, but it does not work. No errors. No records inserted. Just wondering. Thanks. – Anthony Rutledge Jan 25 '16 at 23:01
Inserting Data to temporary table in stored procedure taking too long , While inserting data to a temporary table inside the stored procedure in mysql database.. it is taking too long time (8-11 mins) to insert approx This is the right way to do it. OPENROWSET is pretty much the only way to treat the results of a stored procedure as a table expression. – Rob Farley Aug 5 '09 at 13:24. This seems a bit cumbersome just to insert into a table. A lot of configuring to do. Also when I tried it I got "Msg 7357, Level 16, State 2, Line 1 Cannot process the object
Inserting the results of a stored procedure to a temp table, The simplest way is that temp tables are your friend, and you just insert the results into your temp table: CREATE PROC test_proc. AS Summary: in this tutorial, we will discuss MySQL temporary table and show you how to create, use and drop temporary tables.. Introduction to MySQL temporary tables. In MySQL, a temporary table is a special type of table that allows you to store a temporary result set, which you can reuse several times in a single session.