Bug #12603 INSERT ... SHOW - doesn't work
Submitted: 16 Aug 2005 16:36 Modified: 15 Dec 2010 10:02
Reporter: Matthew Slyman Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:4.1.3 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[16 Aug 2005 16:36] Matthew Slyman 
Description:
I want MySQL to INSERT the list of DATABASES or TABLES into a TABLE as VALUES.  I cannot find any instructions for this on www.mysql.com, and none of my trial queries work.
Can you include INSERT ... SHOW queries in MySQL?

How to repeat:
-- CREATE A TABLE FOR A LISTING OF DATABASES:

CREATE TABLE `database_list` (
  `database_name` varchar(120) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`database_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- THEN TRY THIS:

INSERT INTO database_list (database_name) SELECT Databases FROM SHOW DATABASES

-- OR THIS:

INSERT INTO database_list (database_name) SHOW DATABASES

Suggested fix:
Re-work "INSERT ... SELECT" to cover this?
[15 Dec 2010 10:02] Valeriy Kravchuk 
I think the problem is solved in 5.0+ with introduction of INFORMATION_SCHEMA.