Load Script

[Anonymous]

Hi guys,

I have setup a load script which includes a date/time stamp which I've called sl_date_written

temp_dw_slitemm:

load

  *,

  now() as 'sl_date_written';

sql

select

  customer as 'sl_customer',

  item_no as 'sl_item_no',

  dated as 'sl_dated',

  amount as 'sl_amount'

from

  dbo.sql_table

;

STORE temp_dw_slitemm into Data\dw_slitemm.qvd;

drop table temp_dw_slitemm

What I would like to do is load this into a QVD file but keep the records every day (hope this makes sense?).  At the moment when the QVD loads it overwrites the records.  So I had records with yesterdays date, now after checking today I only have records in the QVD with today's date.

Is there a way to do this via a QV load? If not I will have to create a SQL job to do this

I would appreciate any advice.

Thanks

[jsingh71]

You need to use Incremental Load for this.

See below thread for implementing incremental load:

Incremental reload and store into QVD file

[andrespa]

Hi Jamel,

You could set a variable with the today's date at the end of each store qvd procees. i.e:

STORE temp_dw_slitemm INTO 'C:\yourPath\QVD$(tempVariable).qvd'

Hope it helps you.

Best regards,

Andrés

[Gabriel]

Hi,

You have to adjust your STORE statement slightly

Use something like this

LET vToday = DATE(TODAY(),'YYYY-MM-DD'); // Here you will date stamp your QVD file and it will not overwrite previous //day fiel

STORE RESULT INTO vQVDPath\TableName_$(vToday).QVD(QVD);

Hope this helps

[Peter_Cammaert · Accepted solution]

Before the STORE statement, add this to your script:

:

CONCATENATE (temp_dw_slitemm)

LOAD *

FROM [Data\dw_slitemm.qvd] (qvd);

:

In this way, the internal table will be expanded with all records stored so far, before writing a new version of the QVD to disk.

A true incremental load will first check for updated records and skip loading those from the history file.

Peter

[Anonymous]

Thanks for all your help guys.  Peter's answer was exactly what I had in mind