sql for incremental load [message #407106] |
Mon, 08 June 2009 08:28  |
ajitpal.s
Messages: 204 Registered: November 2006
|
Senior Member |
|
|
Hi,
Is there any way i could to an incremental load with following data. I have a few source tables, which in return i want to populate data to base table, on incremental basis, but each table i have last_update_date. I am not sure how to proceed. I have identified the sql, but is there any way i could capture data incrementally?
create table student (
student_id number,
first_name varchar2(10),
last_name varchar2(10),
flag char(1),
last_update_date date);
create table guardian (
student_id number,
first_name varchar2(10),
last_name varchar2(10),
relationship varchar2(10),
primary char(1),
last_update_date date);
create table address (
student_id number,
street varchar2(10),
state varchar2(10),
city varchar2(10),
country varchar2(10),
primary char(1),
last_update_date date);
Sql to populate the base table
select a.student_id, a.first_name || a.last_name as full_name,
b.first_name || b.last_name as primary_guardian,
c.street || c.state || c.city || c.country as address
from student a, guardian b, address c
where a.student_id = b.student_id
and a.student_id = c.student_id
and b.primary = 'Y'
and c.primary = 'Y'
create table master_student (
student_id number,
full_name varchar2(10),
primary_guardian varchar2(10),
primary_address varchar2(10));
|
|
|
Re: sql for incremental load [message #407143 is a reply to message #407106] |
Mon, 08 June 2009 12:27   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
do you mean load or update?
One way would be to have last_updated_date in the master table too, then
where a.last_update_date >= {some date}
or b.last_update_date >= {some date}
or c.last_update_date >= {some date}
Sorry to say that if you do not have a date in the master table, how would you know when it was loaded?
[Updated on: Mon, 08 June 2009 12:30] Report message to a moderator
|
|
|
|
Re: sql for incremental load [message #407168 is a reply to message #407144] |
Mon, 08 June 2009 22:27   |
ajitpal.s
Messages: 204 Registered: November 2006
|
Senior Member |
|
|
Hi Blackswan and joy_division,
Previously we had a sql created on this 3 tables, and due to the size of this table, it takes some time to return data, and then we plan to create a master student table, and which suits our needs.
Yes, this new table will be populated based on update and insert.
where a.last_update_date >= {some date}
or b.last_update_date >= {some date}
or c.last_update_date >= {some date}
Thats good to add last_update_date in master_student.
The only problem i see with this way, if the guardian name for an existing student has changes, then the last_update_date column for this student in guardian table will change, but since there was no change in the student table, the last_update_date in student table wont change, and i wont be able to pick up this change
|
|
|
Re: sql for incremental load [message #407245 is a reply to message #407168] |
Tue, 09 June 2009 04:29   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You'll have to either:
1) Check the last update dates on all 3 tables
or
2) Have triggers on Address and Guardian to set the date on student if they are updated.
|
|
|
|
|