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

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.

Answer 2

If I am understanding your question correctly, I would do something like this:

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
...

It'll be a big honkin' SQL statement, but you can generate the text in a loop from a quick program in the language of your choice. You could also break it into chunks of say, 50 tables at a time, or just generate 250 separate INSERT statements.

Answer 3

You can do this using dynamic sql, building a string in a while loop and then executing that string.

Or you can be quick and dirty and use excel to do the concatenation of sql strings and then just copy paste from excel and run those strings.

Answer 4

If it has to be done just one time, I'd design a formula in excel/OO spreadsheet, generate the inserts for all 250 tables and be done with it.

Answer 5

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'))