inserting records

[lee]

Hi,
I have about 22 tables... I have created one table which includes all the columns of these 22 tables and one more column called tab_identifier (not null).

Now, I want to insert records in this table from these 22 tables and insert some value in tab_identifier column. How do I do this?

Please explain.

thanks.

[jmodic]

Well, the question is not very clear. Namely, it is not clear if:

a) those 22 tables are equal in their structure, so that the new table has the same structure as any of those 22 tables + tab_identifier column in addition.

b) those 22 tables are different in structure, the number and type and the names of the collumns differ, so that your new table has separate column for each column of each of those 22 tables + tab_identifier column in addition.

If it is option a), then you can do it in a single insert statement:

INSERT INTO new_table (col1, col2, col3, ..., colX, tab_identifier)
(SELECT col1, col2, col3, ..., colX, 'TABLE01' from TABLE01
UNION ALL
SELECT col1, col2, col3, ..., colX, 'TABLE02' from TABLE02
UNION ALL
....
UNION ALL
SELECT col1, col2, col3, ..., colX, 'TABLE22' from TABLE22
);

If it is option b), then the easiest way would be to isue 22 separate INSERT statement, like:

INSERT INTO new_table (tab1_col1, tab1_col2, ..., tab1_colX, tab_identifier)
(SELECTtab1_ col1, tab1_col2, ..., tab1_colX, 'TABLE01' from TABLE01);

INSERT INTO new_table (tab2_colA, tab2_colB, ..., tab2_colY, tab_identifier)
(SELECTtab1_ colA, tab1_colB, ..., tab2_colY, 'TABLE02' from TABLE02);

.....

[lee]

The 22 tables are not equal in structure. But, can I join all 22 of them using select ...... with outer join and then give the command : insert into new_table the select from 2 tables?


Please let me know.

Thanks.

[lee]

I am waiting for a reply. Please reply. It's URGENT.

Thanks.

[jmodic]

No, no, forget about the outer join idea. Even if it was posible, you would have to outer join each of the 22 tables with every other 21 tables - immagine how many combination that is. And what join condition would you use if they have nothing in common?

But the main point is - it is not posible even in theory. There is a restriction in Oracle's outer joins: the same table can not be outer joined with more than one other table in a single select!

What is wrong with 22 separate inserts?

And probably most important thing here: wouldn't you reconsider that strange design of having all columns of 22 tables thrown together into a single huge table? The whole idea sound very bisare.

[Edited by jmodic on 09-03-2001 at 04:19 PM]

[lee]

Actually, the tables do have a column in common, atleast majority of them. Also, one table was created because we had to ask somebody to populate these columns since this was supposed to be a temporary table.

Please let me know if there is any other way, scripts???

Thanks.