Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
That Helps But I wanted with no time stamp.
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
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
Concatenate
Load * ;
Select * FROM `ll_etl`.`test_qv_load`
where product_sk > $(vMaxProduct_sk);
STORE Main into
// full query
if IsNull(QvdCreateTime('$(vQVDPath)')) then
Main:
Select * FROM `ll_etl`.`test_qv_load`;
STORE Main into
Hope it will help others!!! Thank You!