Re: Inserting records from two different sources

fitzjarrell_at_cox.net wrote:
>On Aug 27, 11:29 am, "Lamar Youngers" <lamaryoung..._at_yahoo.com> wrote:

>> I am attempting to INSERT records from two identical tables in two
>> different schemas into one table using a single SQL statement, Could
>> one do this by using DECODE in the INSERT statement?
>>
>> EX:
>>
>> Given tables a, b_at_a, b_at_b, and c, can something like this be done?
>>
>> INSERT INTO c VALUES
>> (DECODE (a.table_schema,
>> 'a',b.column1_at_a,
>> 'b',b.column1_at_b),
>> DECODE (a.table_schema,
>> 'a',b.column2_at_a,
>> 'b',b.column2_at_b),
>> ...
>> );
>> etc.
>
>Where is this table_schema column? How is it populated? If it's in
>the destination table then you can't; it won't work.

I oversimplified my question because I didn't want to get bogged down in details.

Here's some more details:

Table a and c are also in schema a.

Table a contains bank transactions for different departments. The "table_schema" column identifies the department to which the transaction belongs.

Currently, table c is loaded utilizing a cursor with a join between table a and b and a FOR LOOP which performs data manipulation (validation & massaging) before issuing an INSERT statement (with an explicit column list).

We are migrating one department's records to a copy of b in new schema b, so we will need a way to load table c from either b_at_a or b_at_b, depending on the value of the "table_schema" column. I could modify a copy of the stored procedure "Load_c" and name it "Load_c_from_schema_b", but it would be cleaner I think to modify the stored procedure to handle it all.

>A UNION might be a good choice for this, as Brian has already suggested.

I totally overlooked using UNION! Received on Wed Aug 29 2007 - 14:10:27 CDT