SQL / QVD Incremental Load

[goro2010]

Good Day Everyone,

I have an SQL dataset that have over 2 billion lines of data, it took just over 4 hours to download and is just over 2GB in size, I need to update the QVD without downloading the same SQL data everytime but just the changes.

I have a key called "survey_sk" that will be unique between the changes.

I have tried the below but I am getting a syntax error, any help would be appreciated!! - Thank You

"NonTest":

LOAD *, survey_sk as survey_skID

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

Concatenate

SQL select *

FROM "62_DW".dbo."t_test" where not exist(survey_sk, survey_skID);

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;

[ariel_klien]

Hi,

you cannot use QV syntax under SQL sentence.

if survey_sk is a floating number you can try:

"NonTest":

LOAD *

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

Temp:

load max (survey_sk ) as MaxId

resident  NonTest;

let vMaxId=peek('MaxId');

drop table Temp;

Concatenate (NonTest)

SQL select *

FROM "62_DW".dbo."t_test" 

where  survey_sk>$(vMaxId);

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;

BR

Ariel

[goro2010]

Hi,

Thank you for the reply, the issue is that the survey_sk is not floating, but more random, like it could be 3 now and the next line could be 999, so the peek function I have tried and will not work for this, is there a way that I can do it on date, as there is a field to say, date = 20170604, I don't know if there would be a need for me to load the SQL data on a different way and / or to create an "loaded" indicator or something

I have tried most if not all demo's currently available

Thank You

[ariel_klien · Accepted solution]

Yes you can do it on date.

"NonTest":

LOAD *

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

Temp:

load max (Date) as MaxDate

resident  NonTest;

let vMaxDate=peek('MaxDate');

drop table Temp;

Concatenate (NonTest)

SQL select *

FROM "62_DW".dbo."t_test"

where  Date>$(vMaxDate);

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;

if you load partial day you can take the last day like where vMaxDate=peek('MaxDate')-1;

dont forget to load from the qvd date-1

Ariel

[goro2010]

Good Day Ariel,

I have a date like this on the SQL, 20170605 and the Peek function does not see this as an date, is there a way that I can get the max num?

Thank You

[ariel_klien]

yes you can

[Kushal_Chawda]

try like below

NonTest:

LOAD Concat(DISTINCT chr(39) & survey_sk & chr(39),',') as survey_sk

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

let vSurvey_sk = peek('survey_sk',0,'NonTest');

drop table NonTest;

NonTest:

LOAD *

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

Concatenate

SQL select *

FROM "62_DW".dbo."t_test" where not in($(vSurvey_sk));

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;