Can you define "literal" tables in SQL?

Solution 1:

I suppose you could do a subquery with several SELECTs combined with UNIONs.

SELECT a, b, c, d
FROM (
    SELECT 1 AS a, 2 AS b, 3 AS c, 4 AS d
    UNION ALL 
    SELECT 5 , 6, 7, 8
) AS temp;

Solution 2:

You can do it in PostgreSQL:

=> select * from (values (1,7), (2,6), (3,13), (4,12), (5,9) ) x(id, count);
 id | count 
----+-------
  1 |     7
  2 |     6
  3 |    13
  4 |    12
  5 |     9

http://www.postgresql.org/docs/8.2/static/sql-values.html

Solution 3:

In standard SQL (SQL 2003 - see http://savage.net.au/SQL/) you can use:

INSERT INTO SomeTable(Id, Count) VALUES (1, 7), (2, 6), (3, 13), ...

With a bit more chasing, you can also use:

SELECT * FROM TABLE(VALUES (1,7), (2, 6), (3, 13), ...) AS SomeTable(Id, Count)

Whether these work in MySQL is a separate issue - but you can always ask to get it added, or add it yourself (that's the beauty of Open Source).

Solution 4:

In Microsoft T-SQL 2008 the format is:

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)

I.e. as Jonathan mentioned above, but without the 'table' keyword.

See:

FROM (T-SQL MSDN docs) (under derived-table), and
Table Value Constructor (T-SQL MSDN docs)

Solution 5:

I found this link Temporary Tables With MySQL

CREATE TEMPORARY TABLE TempTable ( ID int, Name char(100) ) TYPE=HEAP; 

INSERT INTO TempTable VALUES( 1, "Foo bar" ); 

SELECT * FROM TempTable; 

DROP TABLE TempTable;