CODEWITHSUNDEEP
mysqltemp-tablessql-view
Pratap Reddy
Pratap Reddy

Reputation: 11

Can I create a view on a temporary tables in MySQL?

Is it possible to create a view on a temporary table in MySQL?

Upvotes: 1

Views: 3095

Answers (2)

acarlstein
acarlstein

Reputation: 1838

@O Jones, what you are searching is "The Variable Table"

MySql:

CREATE TEMPORARY TABLE TempTableName (
 column_1 DATATYPE ARGUMENTS, 
 column_2 DATATYPE ARGUMENTS, 
 column_N DATATYPE ARGUMENTS
);

Here is an example on how to use it

CREATE TEMPORARY TABLE WeekDays(
  WeekNumber INT NOT NULL,
  ShortWeekDayName VARCHAR(40) NOT NULL, 
  WeekDayName VARCHAR(40) NOT NULL
);
 
INSERT INTO WeekDays
VALUES 
(1,'Mon','Monday')  ,
(2,'Tue','Tuesday') ,
(3,'Wed','Wednesday') ,
(4,'Thu','Thursday'),
(5,'Fri','Friday'),
(6,'Sat','Saturday'),
(7,'Sun','Sunday'); 

SELECT * FROM WeekDays;

MSSQL:

DECLARE @table_variable_name TABLE (
 column_1 DATATYPE ARGUMENTS, 
 column_2 DATATYPE ARGUMENTS, 
 column_N DATATYPE ARGUMENTS
);

Here is an example on how to use it

DECLARE @WeekDays 
TABLE(
  WeekNumber INT NOT NULL,
  ShortWeekDayName VARCHAR(40) NOT NULL, 
  WeekDayName VARCHAR(40) NOT NULL
);
 
INSERT INTO @WeekDays
VALUES 
(1,'Mon','Monday')  ,
(2,'Tue','Tuesday') ,
(3,'Wed','Wednesday') ,
(4,'Thu','Thursday'),
(5,'Fri','Friday'),
(6,'Sat','Saturday'),
(7,'Sun','Sunday'); 

SELECT * FROM @WeekDays;

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562398

https://dev.mysql.com/doc/refman/8.0/en/create-view.html says:

The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.

Confirmed with a quick test:

mysql> create temporary table foo ( i int );
Query OK, 0 rows affected (0.00 sec)

mysql> create view v as select * from foo;
ERROR 1352 (HY000): View's SELECT refers to a temporary table 'foo'

It makes sense if you think about it. A view should be usable by other sessions. But a temporary table is limited to the current session where it is created. So if you create a view that refers to a table only you can query in your session, it's irrelevant to other sessions and can only return an error.

This is why the statement in the documentation also mentions that you can't create a "temporary view." They are anticipating that you might want a view that is also scoped to your session, that can view tables scoped to your session. But there is no such feature supported by MySQL.

Upvotes: 1

Related Questions