Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Create a temporary table in a MySQL procedure?
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. Following is the query to create a stored procedure and a temporary table in it −
mysql> DELIMITER // mysql> CREATE PROCEDURE create_Temporary_Table() -> BEGIN -> CREATE TEMPORARY TABLE tmpDemoTable SELECT 500; -> END// Query OK, 0 rows affected (0.15 sec)
Following is the query to insert record in the table −
mysql> CREATE PROCEDURE insert_Record_InTempTable() -> BEGIN -> INSERT INTO tmpDemoTable VALUES (300); -> END// Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER
Now you can call the above stored procedure to create a temporary table −
mysql> call create_Temporary_Table(); Query OK, 1 row affected (0.00 sec) mysql> call insert_Record_InTempTable(); Query OK, 1 row affected (0.00 sec)
Display all records from the table using select statement −
mysql> select *from tmpDemoTable;
Output
This will produce the following output −
+-----+ | 500 | +-----+ | 500 | | 300 | +-----+ 2 rows in set (0.00 sec)
Updated on: 30-Jun-2020
2K+ Views
Advertisements