Load only modified fields from a QVD file. Need help

[#1]

Hello everyone,

I would like to put a condition on the LOAD statement, when getting data from a QVD file.

The condition would be to LOAD only the modified fields from the .qvd file.

I have this problem because I don't want to get the hole qvd, because the total amout of data is very large, and I need to get only the modified fields and Load them.

Thank you,

Silviu

[swuehl]

Silviu,

could you please detail how you decide which values / fields are modified? Do you have a modification date in your table or something like this? And against what value do you compare (e.g. do you have a LastDate or something like this)? Then you could probably use a WHERE ModDate >= LastDate statement in your load.

If you want to perform an incremental load, please look also at the example in the QV cookbook, download available here:

http://robwunderlich.com/downloads/

[#3]

Thank you Swuehl fot youre answer.

I read about the incremental load, but I don't thik that this statement can be used in my problem, because I have aprox. 500 tables to load that are modified frequently.

This means that I have to add an extra ModDate field to each table and after that to apply the condition WHERE ModDate >= LastDate.

I thought that there is a solution; when loading, to verify what differences are between the new qvd and the old loaded one.

Any idea will help me.

Silviu.

[Jason_Michaelides]

Hi Silviu,

I  managed to get something like this working a little while ago - give it a go.  When you create the QVD files, combine the fields that are likely to change and wrap in a Hash() function (128, 160 or 256 bit depending on the number of records). Then use WHERE NOT EXISTS (HashField) in the load.

Hope this helps,

Jason

[Jason_Michaelides]

NOTE: this will duplicate your modified records on its own so you'll need another couple of steps to ensure removal of the old records 

[#6]

Hello Janson,

Youre method can be applied also if I'm interested on updated,deleted and inserted rows?

The Hash() function attributes a dimension to some specified fields and after the load statement, QV loads only the fields that have a different dimension from the previous load.

Is it ok what I have understood?

[Jason_Michaelides]

Hi Silviu,

Can you be a little more detailed in your requirements?

Jason