Create the first table, then load each table into the merged_table:
CREATE TABLE merged_table LIKE `1`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `1`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `2`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `3`;
...
...
;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `999`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `1000`;
Here is a way to script it (if the database is mydb)
DB="mydb"
MYSQL_CONN="-uroot -ppassword"
echo "CREATE TABLE merged_table LIKE \`1\`;" > MergeData.sql
SQL="SELECT CONCAT('INSERT INTO merged_table (url,title,content)"
SQL="${SQL} SELECT url,title,content FROM \`998\`')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_name REGEXP "$[0-9]"
mysql ${MYSQL_CONN} -ANe"${SQL}" >> MergeData.sql
Look at the output of the file
less MergeData.sql
If it looks like the output I gave at the top of my answer, then run
mysql ${MYSQL_CONN} -D${DB} < MergeData.sql
Give it a Try !!!
I would lean toward a single Category_Properties table with product_id, category_id, and optionally (if you need to retain the position of the property values in the original design) a sequence number of some sort. You are going to have to do some programming to convert this, but it can be done with some 'brute force' SQL if you are trying to avoid scripting something.
CREATE TABLE Category_Properties
( product_id
, category_id
, sequence_value /* this is optional */
, property_id
);
INSERT INTO Category_Properties
SELECT * FROM (
SELECT product_id, 43 AS cat, 1 AS seq, <first_property_col> AS pval FROM category_properties_43
UNION
SELECT product_id, 43, 2, <second_property_col> FROM category_properties_43
UNION
SELECT product_id, 43, 3, <third_property_col> FROM category_properties_43
)
WHERE pval IS NOT NULL
;
-- From your example this would be <first_property_col> = 17, then 21, then 56.
You can do this provided you know the max number of property columns for each category, the outer query will eliminate the missing values for products which don't use all the property columns. You can see why you might want to script this, rather than changing it for each new category.
Best Answer
Easy enough to do with SQL. You can send the output to a file then execute it once it's verified or just copy and paste the DDL statements back into your client to execute manually.