SQL: combining multiple tables into one

Question

I have 4 tables.

r1, r2, r3 and r4. The columns of the table are as follows:

rId | rName

I want to have, in order, a unique table - let's call it R. Obviously, R will have the following structure:

rTableName | rId | rName

I am looking for a solution, and all the more natural for me is:

• add one column to all rX
• insert this column the name of the table I'm processing
• generate SQL queries and concatenate them all

Although I definitely see how to do 1 and 3 with batch loading, editing, etc. (I should only execute it once and for all), I don’t see how to make point 2: self-service tablename to be inserted into SQL.

Do you have an idea or other way to do this to solve my problem?

Note. Actually there are 250 + rX tables. That is why I cannot do it manually. Note2: Exactly, this is with MySQL.

Answer 1

SELECT 'INSERT R (tTablename, rId, rName)
        SELECT ''' + t.TABLE_NAME + ''', rId, rName
        FROM ' + t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE 'R[0-9]%'
AND 2 = (SELECT COUNT(*)
         FROM INFORMATION_SCHEMA.COLUMNS c
         WHERE c.TABLE_NAME = t.TABLE_NAME
         AND c.COLUMN_NAME IN ('rId', 'rName'))

Answer 2

, - :

INSERT INTO R
SELECT 'r1' AS rTablename, r1.rId, r1.rName FROM r1
UNION
SELECT 'r2' AS rTablename, r2.rId, r2.rName FROM r2
UNION
SELECT 'r3' AS rTablename, r3.rId, r3.rName FROM r3
UNION
...

SQL, . , , 50 , 250 INSERT.

Answer 3

, sql, while .

excel, sql, excel .

Answer 4

, Excel/OO, 250 .

Answer 5

- ?

INSERT INTO rX (rTableName,rId,rName)
SELECT 'R1',rId, rName FROM r1

INSERT INTO rX (rTableName,rId,rName)
SELECT 'R2',rId, rName FROM r2

INSERT INTO rX (rTableName,rId,rName)
SELECT 'R3',rId, rName FROM r3

INSERT INTO rX (rTableName,rId,rName)
SELECT 'R4',rId, rName FROM r4

UPDATE: after viewing your update, this method is not possible.