Re: Inserting records from two different sources

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. A UNION might be a good choice for this, as Brian has already suggested. Presuming you have identical tables in two different schemas INSERT ALL should work:

insert all
into c
(column list here)
select * from a
union all
select * from b;

The UNION ALL would preserve all of the data from both tables, duplicates or not. If you're certain you have no duplicates between the tables or if you don't want anything except unique records then change the 'union all' to 'union'. As an example:

SQL> insert all
  2 into emptest
  3 (empno, ename, job, mgr, hiredate, sal, comm, deptno)   4 select * from emp
  5 union all
  6 select * from emp2;

28 rows created.

Of course you can also be lazy if you know your destination table is defined exactly like your source tables:

SQL> insert all
  2 into emptest
  3 select * from emp
  4 union all
  5 select * from emp2;

28 rows created

Were it me I'd be using an explicit column list.

David Fitzjarrell Received on Wed Aug 29 2007 - 10:57:58 CDT