incremental load

[gferran]

Hi,

I'm loading about 150 milions registers from a QVD and adding more registers from a DB. To do it I propose two ways:

- inner join:

Option1:

load

     a

sql select valid_registers from data_base;

inner join (Option1)

load

     a,    

     x,

     y

from file.qvd;

- Using a where clause:

let Filter=id_to_load

Option2:

load

     a,    

     x,

     y

from file.qvd

where match(a,Filter);

The first option runs ok but is necessary first to load all registers from qvd. I have created the second option looking for a better way to reload but the task doesn't finish.

Any comments?

Thanks

Gabriel

[gferran]

Hi,

I have forgot to say we have about 40 fields in the table.

Gabriel

[maxgro · Accepted solution]

the fastest way from a qvd is with an exists because the load from the qvd is optimized

load

     ...... as a

from

     ...........;

load

     a,   

     x,

     y

from file.qvd

where exists(a);

[jagan]

HI,

Try using exists in where condition like this

Option1:

sql select DISTINCT valid_registers AS a from data_base;

inner join (Option1)

load

     a,   

     x,

     y

from file.qvd

WHERE Exists(a);

DROP TABLE Option1;

Regards,

jagan.

[rwunderlich]

‌The typical pattern in an incremental load is to load only new or changed rows in the SQL. Then when concatenating the existing QVD, use

where not exists(keyfield)

to exclude rows that were loaded in the SQL.

-Rob

[Anonymous]

load the data from database and give the where date > lastupdated date before this one to give conncanate