Error Code: 2008 MySQL client ran out of memory

I have 2 tables with company names (around 1,000 companies) and products (20,000 products).

I am looking to create a table which would have all products against each companies. eg. "company#1" in column A each in rows 1 to 20k... and "products#1 to 20,000" in column B
similarly for company#2" and so on

- so I would have 1,000*20,000 = 20,000,000 records in table.

Thanks

You can use Union to get the type of result you wanted.

I have created DB (myDatabase) with 3 tables (Table1, Table2, Table3) each tables have 1 column - namely company, product1 and and product2 respectively.

table1 has 5 records
table2 has 6000 records
table3 has 2000 records

I am now using below query to create another table and join the results from all 3 tables - which would fetch me 60,000,000 rows (ie 5 * 6000 * 2000). (I know thats huge but I want all records for some analytics)

However when I am running the query I am getting "Error Code: 2008 MySQL client ran out of memory". I am using MySQL workbench on my PC (Win 7-64 Bit, 8GB RAM and 600 GB free HD space) to run the code.

Also if I change the limit (in below code) to lower number (say 1000) it executes the code and shows me results on screen but it does not insert the results into "myNewtable"

Can someone help please.

Create Table myDatabase.myNewtable ( SrNo INT NOT NULL AUTO_INCREMENT PRIMARY KEY, company VARCHAR(255), product1 VARCHAR(255), product2 VARCHAR(55) ); Select table1.company, table2.product1, table3.product2 from myDatabase.table1, myDatabase.table2, myDatabase.table3 LIMIT 0,99999999

your SELECT is a separate statement from your CREATE TABLE

you can define the CREATE TABLE including the SELECT as one statement (by removing the semicolon between them), but i would not do that, because after you've processed a few rows with your LIMIT, you're still going to have to use an INSERT SELECT statement for the remainder

so CREATE the table, and then just start using INSERT SELECT statements

Thanks a lot mate... that helped!
...after 1050 secs I got my data..