Incremental Load without date?

[#1]

Hi I have primary keys in a table:

LOAD

`product_sk`,

    `merchant_sk`,

    `brand_name`,

    `product_id`,

    `product_name`,

    `redemption_type`,

    `product_type`,

    `category_id`,

    `category_name`,

    `release_date`,

    `currency`,

    `net_retail_price`,

    discount,

    `vat_cost`,

    `is_active`,

    `article_id`,

    `product_description`,

    ValueDocumentType;

SQL SELECT *

Here product_sk is primary key can any one please suggest a incremental load using primary key? Please note that in several huge tables we do not have date dimension. I am looking forward for a crashproof script that should not use temp date like Let today's date = UTC, because many days data job failed then I always required to load full table again.

Can you please help me with some script where it will check max product_sk in QVD and tally it with My SQL, then load?

Many Thanks

- Deep

[#2]

Maybe this might be of help

QV_Table:
SQL SELECT Primary Key,X,Y From DB_Table
Where ModificationTime>=#$(LastExecTime)#;

Concatenate LOAD Primary Key,X,Y From File.QVD

WHERE NOT Exists (PrimaryKey);

STORE QV_Table INTO File.QVD;

Regards

Mhatim

[jagannalla · Accepted solution]

Hello,

Here is the code:

OldQVD:

LOAD     

     Product_sk

FROM

Main.qvd (qvd);

MaxOldQVD:

LOAD

Max(Product_sk) as MaxProduct_sk

Resident OldQVD;

LET vMaxProduct_sk = Peek('MaxProduct_sk',-1,MaxOldQVD);

DROP Table OldQVD;

DROP Table MaxOldQVD;

Main:

LOAD *

FROM

Main.qvd;

Concatenate

Load * ;

Select * from dbo.Main

where Product_sk > $(vMaxProduct_sk);

STORE Main into Main.qvd;

Hope it helps you..

Cheers!!

Jagan

[#4]

That Helps But I wanted with no time stamp.

[#5]

It works perfect! Thank You for suggession, I made some changes so that even for 1st time if it do not find main qvd, it will generate. OR It will fetch data from main. Here it is:

Let vQVDPath='C:\Users\...\Main.qvd';

if not IsNull(QvdCreateTime('$(vQVDPath)')) then

OldQVD:

LOAD    

     product_sk

FROM
(qvd);



MaxOldQVD:

LOAD

Max(product_sk) as max_product_sk

Resident OldQVD;





LET vMaxProduct_sk = Peek('max_product_sk',-1,MaxOldQVD);





DROP Table OldQVD;

DROP Table MaxOldQVD;



Main:

LOAD *

FROM

(qvd);

 

Concatenate

Load * ;

Select * FROM `ll_etl`.`test_qv_load`

where product_sk > $(vMaxProduct_sk);



STORE Main into ;



ENDIF

// full query

if IsNull(QvdCreateTime('$(vQVDPath)')) then

Main:

Select * FROM `ll_etl`.`test_qv_load`;

STORE Main into ;



ENDIF

Hope it will help others!!! Thank You!