Re: Development question?

With this type of approach, wouldn't I have a "pair" of statements per customer supplied table? The # of customer supplied tables here could eventually be in the 100s. Trying not to create high-maintanence code for the long run. I WANT to try and have a nightly run which will merge ALL the customer-supplied tables into the merged table to refresh the data within.

Any additional comments/suggestions?

In article <37460396.6487037_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to mdneal_at_my-dejanews.com
> (if that email address didn't require changing)
> On Wed, 19 May 1999 20:10:40 GMT, you wrote:
>
> >Looking for some development suggestions. I need to merge multiple
> >tables containing customer-supplied data into a single table for
> >display on a web front end.
> >
> >I want to merge the tables together into a single table but want to
do
> >inserts only if the record doesn't exist, and updates only if there
is
> >a change to the data. Don't want to re-populate the table each time
I
> >do a merge which would occur nightly. Any development suggestions
> >would be appreciated. I am not a developer but I am helping out on
> >a "hot fire" project. I am from the DBA world.
> >
>
> Here is how to do that. You can use a single UPDATE/INSERT pair of
statements.
> Assume 'new_data' represents one of your customer supplied data
tables.
> merged_data is the single table. It might look like:
>
> SQL> create table new_data( x int primary key,
> 2 data varchar2(255) );
> Table created.
>
> SQL> create table merged_data( y int primary key,
> 2 mdata varchar2(255) );
> Table created.
>
> SQL> insert into new_data values ( 1, 'First Go Around' );
> SQL> insert into new_data values ( 2, NULL );
> SQL> insert into new_data values ( 3, NULL );
>
> SQL> -- works as long as chr(0) is not a valid value for data!!
> SQL> --
> SQL> -- can use:
> SQL> -- ( ( x <> y ) OR
> SQL> -- ( x is not null and y is null ) OR
> SQL> -- ( x is null and y is not null ))
> SQL> --
> SQL> -- instead of nvl...
> SQL>
> SQL> update
> 2 ( select *
> 3 from new_data, merged_data
> 4 where new_data.x = merged_data.y
> 5 and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr
(0))
> 6 )
> 7 set mdata = data
> 8 /
>
> 0 rows updated.
>
> SQL>
> SQL> insert into merged_data
> 2 select *
> 3 from new_data
> 4 where not exists ( select NULL
> 5 from merged_data
> 6 where merged_data.y = new_data.x )
> 7 /
>
> 3 rows created.
>
> SQL> select * from merged_data
> 2 /
>
> Y MDATA
> ---------- ----------------------------------------
> 1 First Go Around
> 2
> 3
>
> SQL> insert into new_data values ( 4, 'Second Go Around' );
> SQL> update new_data set data = NULL where x = 1;
> SQL> update new_data set data = 'Updated from NULL' where x = 2;
>
> SQL> update
> 2 ( select *
> 3 from new_data, merged_data
> 4 where new_data.x = merged_data.y
> 5 and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr
(0))
> 6 )
> 7 set mdata = data
> 8 /
> 2 rows updated.
>
> SQL> insert into merged_data
> 2 select *
> 3 from new_data
> 4 where not exists ( select NULL
> 5 from merged_data
> 6 where merged_data.y = new_data.x )
> 7 /
>
> 1 row created.
>
> SQL> select * from merged_data
> 2 /
>
> Y MDATA
> ---------- ----------------------------------------
> 1
> 2 Updated from NULL
> 3
> 4 Second Go Around
>
> >Thanks in advance,
> >
> >Mike Neal
> >
> >
> >--== Sent via Deja.com http://www.deja.com/ ==--
> >---Share what you know. Learn what you don't.---
>
> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
Oracle8i'...
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Thu May 20 1999 - 09:58:50 CDT