with out comparing destnation table what are the ways to do incremental loading

Question

with out comparing destnation table what are the ways to do incremental loading

guru12

Ten Centuries

Points: 1068
More actions
July 6, 2010 at 6:26 am

#157059

Hi everyone..
My manager ask me to do incremental loading with out comparing to the destination table,i search in google but i got solutions using lookup,conditional split and these will compare to destination,Is there any solution to achieve this ,please let me know ASAP,its very urgent.By the way we have modified date column in oltp database.
Regards..
Anil...
Regards..
guru12

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Dave Morrison SSCrazy Points: 2077 More actions · Answer 1

A work around would be to create a table with one column called last load and put the date and time of the last load you did.

Then simply only incrementally load anything that has a modified datestamp greater than that value and update the last load value ready for the next time the package runs.

Hope this makes sense

Thanks

guru12 Ten Centuries Points: 1068 More actions · Answer 2

Hi Dave..

Thank you for your valuable suggestion,rather than this if you have any solutions please post.I will show different solutions to my manager .

Regards..

Anil...

Regards..
guru12

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

Hi Anil,

The very ground basis of Incremental loading is based upon the fact that one persorms a lookup operation on the destination table---->determine change--->load change.

Your thought's of your manager in this case is superflous....

Raunak J

Dave Morrison SSCrazy Points: 2077 More actions · Answer 4

Anil, really without referencing the base table this is the only real way around it as you can never know what you have already loaded

I suupose you could go with a slightly more brute force approach which is to simply empty the destination table and reload the whole file each time, although I dont really recommend it.

I do have to agree with the other poster to some extent that it is an odd request to do an incremental load without knowing what you have previously loaded.

Have you challeged why you cannot refernce the table?

Thanks

guru12 Ten Centuries Points: 1068 More actions · Answer 5

Hi Dave..

We have millions of records ,so comparing with reference table must take more time,so we dont want to lookup,i will try with that solution what you have given earlier,If i got strucked any where i will let you know.

Regards..

Anil..

Regards..
guru12

Dave Morrison SSCrazy Points: 2077 More actions · Answer 6

npothineni (7/6/2010)
Hi Dave..
We have millions of records ,so comparing with reference table must take more time,so we dont want to lookup,i will try with that solution what you have given earlier,If i got strucked any where i will let you know.
Regards..
Anil..

Hi Anil,

I will be honest, even with hundreds of millions of rows doing a max on a date column that is properly indexed will take no time at all.

As a test, try somethign like below, if your clustered index is on the datestamp field in both tables I'd bet it'll be pretty quick. even a non clustered index and I dont think it would take all that long.

insert into destinationtable

select * from sourcetable

where datemodified not in (select datemodified from destinationtable)

Obviously this assumes that no two rows are stamped with exaclty the same datetime but the principle holds if you have a unique indentity column or something, providing again they are indexed correctly

Give it a go and let me know how you get on

Thanks

Dave

guru12 Ten Centuries Points: 1068 More actions · Answer 7

Hi Dave..

We used i used the insert method what you have given,it works fine.thank you so much for your support.If any doubts i will post here please reply.

Regards..

Anil...

Regards..
guru12

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 8

Hi Anil,

I still do not understand how does the SQL statement you implemented works fine...you still are refering the destination table.

And as mentioned earlier..this is brute force loading...

Raunak J

guru12 Ten Centuries Points: 1068 More actions · Answer 9

Hi Raunak..

Yes i refered to the destination table andd performance is good,so they agreed with that,i fyou have any solution without referencing the destination table please post..

Regards..

Raunak..

Regards..
guru12

Dave Morrison SSCrazy Points: 2077 More actions · Answer 10

Dave Morrison

SSCrazy

Points: 2077

July 7, 2010 at 3:13 am

#1189492

Hi Anil,

I'm glad this helped

All the best

Dave

charlesbc SSC Journeyman Points: 77 More actions · Answer 11

If your source table is managed by a MS SQL Server 2008, then you can alter its database to allow Change Tracking. When doing so, you can also set the change retention period and auto-cleanup property. After altering the database, the source table is altered to enable change tracking (and an internal change data capture table will be created for it).

When insert,update, delete statements execute against the source table, those changes are automatically captured by the SQL server and recorded in the source table's change data capture table (so, therefore, you do not need to modify any existing CRUD statements to work with the internal change data capture table).

You then merely query the change capture table to obtain the changes, which you then apply to your destination table.