Incremental load script

[madhuqliklondon]

Hi Friends,

I have created .qvd file and done initial load by below script

Amount:

Load

  [idDate]

,[idArticle]

,[idStore]

,[idCustomer]

,[Qty]

,[NetAmount]

,[BrutAmount]

,[MarginAmt]

,[Rank_ArtCust];

sql select * from [wpreplic].[dbo].[WHISTOMVT];

store Amount into C:\Users\madhu\Desktop\Qvd\Amount.qvd(qvd);

DROP Table Amount;

How can write incremental load script for this , the source(sql) is getting updated once in a day.

Thank you

[madhuqliklondon]

Sqlserver

[qv_testing · Accepted solution]

Hi Madhu,

Before doing Incremental load convert date to 111 format.

Date should be YYYY/MM/DD, application will compare fastly

SET vQvdFile='TableName.QVD';

SET vTableName='TableName';

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN

maxdateTab:

LOAD Max(Date(YourDate,'YYYY/MM/DD')) as maxdate

FROM $(vQvdFile) (qvd);

LET vIncrementalExpression = 'where Convert(varchar(10), ("YourDate"),111) >' & Chr(39) & peek('maxdate') & Chr(39);

DROP Table maxdateTab;

ELSE

LET vIncrementalExpression = '';

END IF

$(vTableName):

LOAD

[idDate]

,[idArticle]

,[idStore]

,[idCustomer]

,[Qty]

,[NetAmount]

,[BrutAmount]

,[MarginAmt]

,[Rank_ArtCust];

SQL select * from [wpreplic].[dbo].[WHISTOMVT];;

$(vIncrementalExpression);

IF $(vQvdExists) THEN

CONCATENATE ($(vTableName))

LOAD * FROM $(vQvdFile) (qvd);

End IF

STORE $(vTableName) INTO TableName.QVD (QVD);

DROP Table $(vTableName);

[Kushal_Chawda]

then this should work, please check the syntax

[madhuqliklondon]

Sure I will check. Thank you for your help.

[madhuqliklondon]

Thank you.