topic Re: Incremental Load from SQL table Simple in Management & Governance

Incremental Load from SQL table Simple

vikasmahajan — Wed, 08 Nov 2017 06:05:01 GMT

Hi all ,

I have this SQL statement I Wanted to apply incremental load on the same TRX_DATE is a transaction date for the fact table

how to do this please guide ?

Select

[MERCHANT_ID], b.ACCOUNT_ID

,[TERMINAL_ID]

,[BATCH_ID]

,[STAFF_ID]

,[AMOUNT]

,[RATE]

,[VOLUME]

,[VEHICLE_NO]

,[TRX_DATE]

,[SETTLEMENT_DATE]

,[NOZZLE_ID]

,[VEHICLE_SEGMENT]

,[TOTALIZER_READING]

,[TRX_START_DATE]

,[PRESET_TYPE]

,[PRESET_VALUE]

,b.AUTOMATION_TYPE

,b.ME_SAPCC as CCNO

,b.ME_NAME

,b.ME_STP

,b.ME_CITY

FROM [RA].[dbo].[RO_TRANSACTION_DETAILS] a

Inner Join [RA].[dbo].[RA_ACCOUNTS] b ON a.MERCHANT_ID = b.ACCOUNT_ID ;--b.ACCOUNT_TYPE='SA';

--where TRX_DATE >= '01-OCT-2017' and TRX_DATE <= '2-Oct-2017';

Vikas

Re: Incremental Load from SQL table Simple

shraddha_g — Wed, 08 Nov 2017 06:07:53 GMT

Normal Incremental Load script and format the Date according to that of TRX_DATE.

where TRX_DATE >= '01-OCT-2017' and TRX_DATE <= '2-Oct-2017';

This line will have variables instead of Oct dates.

Re: Incremental Load from SQL table Simple

vikasmahajan — Wed, 08 Nov 2017 06:20:40 GMT

Hi Shraddha ,

In this table we have millions of records , seeking solutions for incremental load which will capture only

latest records while pulling from SQL.

Can you please share script or some example if you have ?

Vikas

Re: Incremental Load from SQL table Simple

shraddha_g — Wed, 08 Nov 2017 06:36:21 GMT

You will have to fetch one tym records for some date.(Say you have fetch records till 2nd Oct 2017) and store it in qvd as Trx.Qvd

Try below:

MAXData:

Load

Max(TRX_DATE) as Last_Trx_Date

From .....Trx.qvd;

let vMinInc= num(peek('Last_Trx_Date'));

let vMaxInc=num(today());/* Max Date for inc load Load*/

Drop table MAXData;

/* Creating all dates from Min to Max */

Temp:
Load

date($(vMinInc) + RowNo() -1) as Date,
AutoGenerate 1

While date($(vMinInc) + RowNo() -1) < date($(vMaxInc));

/* Loading Temp table to calculate no of rows*/

TEMP:
Load Distinct
Date
Resident Temp;

Drop table Temp;

Let vCount = NoOfRows('TEMP'); /*calculate no of rows i.e no of Dates*/

For i=0 to $(vCount)-1; /* loop to start with concatenating all Daily data*/

let vDate = date(peek('Date',$(i),'TEMP'),'DD-MMM-YYYY');

TableName:

Select

[MERCHANT_ID], b.ACCOUNT_ID

,[TERMINAL_ID]

,[BATCH_ID]

,[STAFF_ID]

,[AMOUNT]

,[RATE]

,[VOLUME]

,[VEHICLE_NO]

,[TRX_DATE]

,[SETTLEMENT_DATE]

,[NOZZLE_ID]

,[VEHICLE_SEGMENT]

,[TOTALIZER_READING]

,[TRX_START_DATE]

,[PRESET_TYPE]

,[PRESET_VALUE]

,b.AUTOMATION_TYPE

,b.ME_SAPCC as CCNO

,b.ME_NAME

,b.ME_STP

,b.ME_CITY

FROM [RA].[dbo].[RO_TRANSACTION_DETAILS] a

Inner Join [RA].[dbo].[RA_ACCOUNTS] b ON a.MERCHANT_ID = b.ACCOUNT_ID ;--b.ACCOUNT_TYPE='SA';

--where TRX_DATE = '$(vDate)' ;

Concatenate

Load *

From ....Trx.qvd;

Store Tablename into ....Trx.qvd;

Re: Incremental Load from SQL table Simple

vikasmahajan — Wed, 08 Nov 2017 06:41:02 GMT

Thanks I will try and let you know

Re: Incremental Load from SQL table Simple

shraddha_g — Wed, 08 Nov 2017 06:45:41 GMT

sure