1

I've created a temporary table DETAILS and follow the same syntax of creating and inserting in it. But I have not received any result set However, the CREATE and INSERT statements ran successfully and the Row was also affected in the INSERT statement . But the result set was empty when I ran the last SELECT statement to view the record .

DROP TABLE DETAILS ;
CREATE GLOBAL TEMPORARY TABLE DETAILS AS (
SELECT ins_id , firstname , pages FROM 
INSTRUCTOR)DEFINITION ONLY;

INSERT INTO DETAILS
SELECT ins_id , firstname , pages 
FROM INSTRUCTOR WHERE ins_id = '1';

SELECT * FROM DETAILS ;
Ali
  • 35
  • 1
  • 5
  • *the CREATE ... statements ran successfully* - I don't believe, its text contradicts [MySQL 8.0 Reference Manual / ... / CREATE TABLE Statement](https://dev.mysql.com/doc/refman/8.0/en/create-table.html). PS. There is NO global temptables in MySQL. – Akina Jun 05 '20 at 07:08
  • CREATE GLOBAL TEMPORARY TABLE - exists in oracle - this question may be incorrectly tagged. – P.Salmon Jun 05 '20 at 07:16
  • What's in your `INSTRUCTOR` table? What does it return when you run `SELECT ins_id , firstname , pages FROM INSTRUCTOR WHERE ins_id = '1';`? Kindly re-tag your Question to reflect your actual RDBMS. – Scratte Jun 05 '20 at 09:03
  • INSTRUCTOR is a simple table holding the records of all Instructors having just 5 columns and 10 records > firstname , lastname , pages , books and ins_id(Pk). . – Ali Jun 05 '20 at 17:04

1 Answers1

1

If you want to preserve rows in CGTT after commit, you have to specify ON COMMIT PRESERVE ROWS option of the CREATE GLOBAL TEMPORARY TABLE statement.
ON COMMIT DELETE ROWS option is in effect otherwise, and such a table is cleared on commit.

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16