Incremental Load from SQL table Simple

[vikasmahajan]

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

[shraddha_g]

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.

[vikasmahajan]

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

[shraddha_g]

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)' ;

Next

Concatenate

Load *

From ....Trx.qvd;

Store Tablename into ....Trx.qvd;

[vikasmahajan]

Thanks I will try and let you know

[shraddha_g]

sure