SQL: joining multiples tables into one

Question

I have 4 tables.

r1, r2, r3 and r4. The table columns are the following:

rId | rName

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

rTableName | rId | rName

I'm looking for a solution, and the more natural for me is to:

1. add a single column to all rX
2. insert this column the table name i'm processing
3. generate SQLs and concatenate them all

Although I see exactly how to perform 1 and 3 with batching, editing, etc... (I have only to perform it once and for all), I don't see how to do the point 2: self-getting the tablename to insert into SQL.

Have you an idea / or a different way to do that could solve my problem?

Note: In fact, there are 250+ rX tables. That's why I can't do this manually. Note2: Precisely, 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

Would something like this work?

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 seeing your update this method isn't feasible.