Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm trying to do a Incremental Insert and Update Load on Update Date. I'm getting a syntax error. Can someone provide assistance.
Thanks in Advance,
Try this in 2 reloads:
1) reload with this script
SET vQVDLocation=' C:\QV Project\Laura\Data';
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
ODBC CONNECT TO ExcessionProd;
CustomerCuration:
SQL SELECT *
FROM "rbc_dw"."open".dim1customercuration;
STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
DROP Table CustomerCuration;
2) once you have the qvd created, comment out the above script and replace it with this one
//SET vQVDLocation=' C:\QV Project\Laura\Data';
//LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
//ODBC CONNECT TO ExcessionProd;
//
//CustomerCuration:
//SQL SELECT *
//FROM "rbc_dw"."open".dim1customercuration;
//
//STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
//
//DROP Table CustomerCuration;
SET vQVDLocation=' C:\QV Project\Laura\Data';
ODBC CONNECT TO ExcessionProd;
CustomerCuration:
SQL SELECT *
FROM "rbc_dw"."open".dim1customercuration
Where updatedate > '$(vLastRunTime)';
Concatenate(CustomerCuration)
LOAD *
FROM customercuration.qvd (qvd)
Where not exists (pkdim1customercuration);
STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
DROP Table CustomerCuration;
Why are you doing SQL SELECT * for QVD load?
May be you want this instead
Concatenate
LOAD *
FROM customercuration.qvd (qvd)
Where not Exists (pkdim1customercuration);
maybe (bold = change)
ODBC connect.....
CustomerCurationIU
SQL Select ........;
concatenate (CustomerCurationIU)
load *
from .......qvd (qvd)
where not exists (pk....);
store.......
drop......
Guys I'm new to this so please forebear with me. Your correction works great, With this Insert and Update Load will I lose my old data? I loaded and it showed only 138 rows while I know the DB table has around 200K rows.
Please provide insights.
Thanks
You should not. The only thing you should loose from the qvd are the new pkdim1customercuration which you are updating with new data from the SQL. So if your qvd had 10,000 rows of data and the SQL updated 10 of them and 10 were new. You should have 10010 rows of data in the qvd after the reload.
you have a where updatedate > ...... in the
SQL select ......
maybe it filter the rows from your database
to check how many rows you get from the db, comment the last part of the script and try
ODBC connect.....
CustomerCurationIU
SQL Select ........;
//concatenate (CustomerCurationIU)
//load *
//from .......qvd (qvd)
//where not exists (pk....);
//store.......
//drop......
It only shows 138 rows
I still get 138 rows. Still don't know the reason.
As maxgro 1st create a new qvd using
One Time:
ODBC connect.....
CustomerCurationIU
SQL Select ........;
STORE CustomerCurationIU into customercuration.qvd (qvd);
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
This will tell you how many rows of data you have.
Now setup your incremental load
Incremental:
ODBC connect.....
CustomerCurationIU
SQL Select ........
Where updatedate > '$(vLastRunTime)';
Concatenate
LOAD *
FROM CustomerCurationIU
WHERE not Exists(pkdim1customercuration);
STORE CustomerCurationIU into customercuration.qvd (qvd);
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
I still don't get the big chunk except for the 138 rows.