Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to do incremental update (Oracle 10g and Windows 2003)
Procedure to do incremental update [message #442303] Sat, 06 February 2010 12:50 Go to next message
aijaz786
Messages: 91
Registered: February 2010
Member
I need to update or insert my staging table from data in External table every morning. I any new records appears in External table then it is inserted and if a recrod from external table is found in staging table then two operations are suposed to performned, one insert and other update with the same record.

I mean handing type 2 dimension using Oracle stored procedure/package.

Please help me.

Aijaz
Re: Procedure to do incremental update [message #442304 is a reply to message #442303] Sat, 06 February 2010 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Procedure to do incremental update [message #442308 is a reply to message #442303] Sat, 06 February 2010 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only thing that can be said from what you posted is that it can be done.
The way to do it depends on many parameters you didn't post stared from a clear test case showing the different cases and result you want.

Regards
Michel
Re: Procedure to do incremental update [message #442316 is a reply to message #442303] Sat, 06 February 2010 22:01 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Test Case:

External Table structure

Col1 Number
Col2 Date
Col3 Date
Col4 varchar2(10)


Target Table Structure


Col1 Number
Col2 Date
Col3 Date
Col4 varchar2(10)
active_flag varchar2(1)
date_from date


First time, all records from External table will be loaded into target table. After first day, incremental update witll be done using updated data file for External table.

If a record in external table is not found in target tbale then it will be inserted into target table as is.

if record in external table is found in target table then there will be two operations to be performed

A) Record will be inserted with flag active=N
2) record will be updated with flag acitve = 'Y' and date_from = current date

A procedure is to be created that could handle all this operation with effective error control.

Thanks.



Re: Procedure to do incremental update [message #442318 is a reply to message #442316] Sat, 06 February 2010 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Procedure to do incremental update [message #442325 is a reply to message #442316] Sat, 06 February 2010 23:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In short, all rows from external table are inserted and then rows in table with same cols* than in external have to be update with active=N.

So 2 statements:
1/ insert all rows
2/ update those that are in external table and from another date

Regards
Michel
Re: Procedure to do incremental update [message #442344 is a reply to message #442303] Sun, 07 February 2010 01:26 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
External Table structure

Col1 Number
Col2 Date
Col3 Date
Col4 varchar2(10)


Target Table Structure


Col1 Number
Col2 Date
Col3 Date
Col4 varchar2(10)
active_flag varchar2(1)
date_from date


Let me clarify more. Structurs of both the tables is same except two addition columns in staging table
i.e. active_flag and date_from.

Let us suppose, I start ETL process today. I receive datafile today and I load data into external table. All data in external table is loaded (inital load) and then inserted into staging table.


Second day (tomorrow), I receive another data file and it loaded again in external table. Now this external table contains data from this datafile not from previous day.

using this externaltable, I need to insert or update record in staging table based on record from external table exists or not exists in staging table.

Basically it is situation that is called type-2 dimension.

Thanks.



Re: Procedure to do incremental update [message #442346 is a reply to message #442344] Sun, 07 February 2010 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to insert or update record in staging table based on record from external table exists or not exists in staging table.

This is not what you previously said:
Quote:
If a record in external table is not found in target tbale then it will be inserted into target table as is.

if record in external table is found in target table then there will be two operations to be performed

A) Record will be inserted with flag active=N
2) record will be updated with flag acitve = 'Y' and date_from = current date


If your new requirement is correct then use MERGE.

Regards
Michel
Re: Procedure to do incremental update [message #442349 is a reply to message #442346] Sun, 07 February 2010 02:11 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Thanks.

I need to implement this ligic using Oracle stored procedure using an effective exception control.

I will appreciate if somebody let me how to do the same using PL/SQL stored prcoedure.

Regards
Re: Procedure to do incremental update [message #442350 is a reply to message #442349] Sun, 07 February 2010 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already told how to do it for both requirements.
Please try to do it yourself now and come back if you can't (without forgetting to post whar BlackSwan asked, see test case).

Regards
Michel
Re: Procedure to do incremental update [message #442412 is a reply to message #442346] Sun, 07 February 2010 21:58 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi,

If a record in external table is not found in target tbale then it will be inserted into target table as is.

if record in external table is found in target table then there will be two operations to be performed

A) Record will be inserted with flag active=N
2) record will be updated with flag acitve = 'Y' and date_from = current date

If the record is completely new in external table that does not exist in target table then we can insert that record straigtforward.

In case record in External table is found in target table then how can we use MERGE to perform

A) Record will be inserted with flag active=N
2) record will be updated with flag acitve = 'Y' and date_from = current date


Please advise and if possible with code.

Regards.
Re: Procedure to do incremental update [message #442413 is a reply to message #442412] Sun, 07 February 2010 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>Please advise and if possible with code.
You are advised that we won't do your (home)work for you.
Yes, it is possible.

>A) Record will be inserted with flag active=N
>2) record will be updated with flag acitve = 'Y' and date_from = current date

Why do INSERT & then UPDATE?
Why not just INSERT with flag acitve = 'Y' and date_from = current date?
Re: Procedure to do incremental update [message #442414 is a reply to message #442413] Sun, 07 February 2010 22:16 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
To maintain histroy.

Regards.
Re: Procedure to do incremental update [message #442415 is a reply to message #442414] Sun, 07 February 2010 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
aijaz786 wrote on Sun, 07 February 2010 20:16
To maintain histroy.


I think I understand what audit trail history is.
>2) record will be updated with flag acitve = 'Y' and date_from = current date

I contend that at any ponit in time after COMMIT of UPDATE; you have no history that " inserted with flag active=N" ever existed.

If you do two INSERT, then history would exist.
UPDATE erases/overwrites history!

EXACTLY what history is maintained by UPDATE?
Re: Procedure to do incremental update [message #442416 is a reply to message #442415] Sun, 07 February 2010 22:44 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member

If a record in a source table already exists in target table then we need do two things:

There will be two versions of this record:

Insert this record in target table with same columns with active_flag='N'
update the same record with changed values and set active_flag='Y'


If the record is brand new then we will insert as is with active_flag='Y'


source table

col1 col2 col3 col4

01 111 222 444
02 222 333 555


target tabel

col1 col2 col3 col4 active_flag date_from

01 111 666 555 'Y' 01/01/2009
03 999 999 999 'Y' 01/01/2009



where col1 is primary key


Now after ETL process is completed then we shoudl have following in target table:



col1 col2 col3 col4 active_flag date_from

01 111 666 555 'N' 08/02/2010
01 111 222 444 'Y' 08/02/2010
03 999 999 999 'Y' 01/01/2009
02 222 333 555 'Y' 08/02/2010
Re: Procedure to do incremental update [message #442419 is a reply to message #442416] Sun, 07 February 2010 23:00 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
please post your code that attempts to satisfy the requirements.

You are advised that we won't do your (home)work for you.
Re: Procedure to do incremental update [message #442420 is a reply to message #442419] Sun, 07 February 2010 23:06 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi

I am not getting idea how to develop code to implement this logic. May be somebody had already created code for this type of requirements.

I will appreciate.

Rds.
Re: Procedure to do incremental update [message #442421 is a reply to message #442420] Sun, 07 February 2010 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
http://asktom.oracle.com contains many fine coding examples & as does this forum is you just SEARCH
Re: Procedure to do incremental update [message #442440 is a reply to message #442420] Mon, 08 February 2010 01:12 Go to previous message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not getting idea how to develop code to implement this logic

I posted you how to do it in 2 statements.
Did you try these 2 statements?

Once more provide a test case if you want us to show you code.

Regards
Michel
Previous Topic: Logic
Next Topic: Find out date range for last 4 and 52 weeks
Goto Forum:
  


Current Time: Fri Sep 30 21:00:34 CDT 2016

Total time taken to generate the page: 0.08390 seconds