How to do incremental update?

[rsrinivasan]

Hi all,

i have to update data from transactional table to my staging table. i want the data to be update incrementally from t_table to staging table. And also if any update is happen in t_table that records also updated in my staging table. How to do it?

Thanks,

[ck9663]

What do you mean "incrementa l" ?

-- CK

[Delerna]

You need date and time for each transaction in the transactional table.
Use dts to download from the transactional table into an intermediate table.
The dts needs to
1) Delete the records from the intermediate table
2) Get the date and time since the last download.
3) Download the records after that date and time from the transactional table into the intermediate table
4) Save the date and time of this download
4) Execute a stored proc
6) schedule the DTS to run as required

The stored proc needs to
1) find all records in the intermediate table that are already in the Stageing table
2) Update them accordingly
3) find all records in the intermediate table that are not in the stageing table and insert them


NOTE
If the stored proc has direct access to the transaction table you should be able to place the functionality of the DTS into the stored proc.
Hope it helps you

[rsrinivasan]

You need date and time for each transaction in the transactional table.
Use dts to download from the transactional table into an intermediate table.
The dts needs to
1) Delete the records from the intermediate table
2) Get the date and time since the last download.
3) Download the records after that date and time from the transactional table into the intermediate table
4) Save the date and time of this download
4) Execute a stored proc
6) schedule the DTS to run as required

The stored proc needs to
1) find all records in the intermediate table that are already in the Stageing table
2) Update them accordingly
3) find all records in the intermediate table that are not in the stageing table and insert them


NOTE
If the stored proc has direct access to the transaction table you should be able to place the functionality of the DTS into the stored proc.
Hope it helps you

Thanks for your reply..

The transational table does not have any date and time field. The t_table has millions of record and some records(10 to 20) are updated daily. And some records are inserted into t_table daily.

Currently we delete all records in staging table and DTS all records from t_table.
And it takes around 2 hrs. So i have to implement incremental model to update the staging table.

Thanks,

[Delerna]

Ok, well you need some way to tell which records have been updated or changed since the last incremental update. If not a date time field then is there some other field that you can use that says "I am new" ?

[ck9663]

Oh, that kind of incremental :)

What Delerna is saying is that you need an identifier or a flag that will say it is a new record. The problem with the flag is you can not identify which one is newer, unless you use lots of incremental value for your flag (increment by one if updated) and compare it with your main table. If the main table has a lower value, it's a new row. A date field might still be the best way.

-- CK

[rsrinivasan]

Oh, that kind of incremental :)

What Delerna is saying is that you need an identifier or a flag that will say it is a new record. The problem with the flag is you can not identify which one is newer, unless you use lots of incremental value for your flag (increment by one if updated) and compare it with your main table. If the main table has a lower value, it's a new row. A date field might still be the best way.

-- CK

It is not possible to add a field in t_table. Because it is third party table. We can only access through select statement and insert into our staging table. So i need some other better way to sync it.

[ck9663]

Since it's your local copy, can you alter the structure and add those dates?

-- CK

[rsrinivasan]

Since it's your local copy, can you alter the structure and add those dates?

-- CK

Ya. I can alter my staging table structue. But i can't be able to alter t_table structure.