incremental load - insert only

[arulsettu]

hi

i am trying to do incremental load only in my app.

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Arul\Desktop\my project\CUSTOMERS.xls];

Customer:

SQL SELECT *

FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

Customer:

LOAD [Address Number],

  [Business Family],

  [Business Unit],

  Customer,

  [Customer Number],

  [Customer Type],

  Division,

  Phone,

  [Region Code],

  [Regional Sales Mgr],

  [Search Type];

SQL SELECT * FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`

WHERE [Customer Number] >='10027629'; 

while loading this i am getting error. can anyone help?

thanks...

[#2]

First you have to do an intial load

//INITIAL LOAD

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Arul\Desktop\my project\CUSTOMERS.xls];

Customer:

SQL SELECT *

FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

Now all your data is stored into your customer.qvd - Then comment out the intial load part

MaxID:

LOAD Max([Customer Number]) as MaxIncrementalID

FROM

C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

LET vMaxIncrementalID = peek('MaxIncrementalID',0,'MaxID');

Now you need to do your incremental load

//INCREMENTAL LOAD

Customer:

LOAD [Address Number],

  [Business Family],

  [Business Unit],

  Customer,

  [Customer Number],

  [Customer Type],

  Division,

  Phone,

  [Region Code],

  [Regional Sales Mgr],

  [Search Type];

SQL SELECT * FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`

WHERE [Customer Number] >=$(vMaxIncrementalID);

Concatenate

LOAD

[Address Number],

  [Business Family],

  [Business Unit],

  Customer,

  [Customer Number],

  [Customer Type],

  Division,

  Phone,

  [Region Code],

  [Regional Sales Mgr],

  [Search Type]

FROM

C:\Users\Arul\Desktop\my project\Customer.qvd (qvd);

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

[arulsettu]

so why is the QVD getting updated without concatenate any help?

[arulsettu]

thanks  i ll check and comeback to u

[#5]

remember first to load the intial load (comment out all the other)

[arulsettu]

after initial load commenting initial load means this:

//Customer:

//SQL SELECT *

//FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

//

//STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

[#7]

Yes!

[arulsettu]

any reason for this

[its_anandrjs]

You can try with this way also

//Step One (Creating the QVD) After creating the QVD comment this step one it is only for initial QVD creation

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Arul\Desktop\my project\CUSTOMERS.xls];

Customer:

SQL SELECT *

FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`;

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

DROP Table Customer;

///////////////////////////////////////////////////////////////////

//Step Two (Find out the Max Incremental Load ID

MaxIDTable:

LOAD Max([Customer Number]) as MaxIncrID

FROM

C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

LET vMaxIncrID = peek('MaxIncrID',0,'MaxIDTable');

//Step Three (Incremental Load Step and concatenate with the old data with QVD

Customer:

LOAD [Address Number],

  [Business Family],

  [Business Unit],

  Customer,

  [Customer Number],

  [Customer Type],

  Division,

  Phone,

  [Region Code],

  [Regional Sales Mgr],

  [Search Type];

SQL SELECT * FROM `C:\Users\Arul\Desktop\my project\CUSTOMERS.xls`.`CUSTOMERS$`

WHERE [Customer Number] >=$(vMaxIncrID);

Concatenate

LOAD

[Address Number],

  [Business Family],

  [Business Unit],

  Customer,

  [Customer Number],

  [Customer Type],

  Division,

  Phone,

  [Region Code],

  [Regional Sales Mgr],

  [Search Type]

FROM

C:\Users\Arul\Desktop\my project\Customer.qvd (qvd);

STORE Customer into C:\Users\Arul\Desktop\my project\Customer.qvd(qvd);

DROP Table MaxIDTable;

[#10]

Yes! If you wanna do incremental loads, you first need to load all your data. Hereby you can fetch the maximum ID you wanna use as an incremental identifier.

This ID you find the MAX from which you can use when you wanna load from your source.

So now you only get the fields which is greather than or equal to your maxID (If it was me i wanna remove the equal too, otherwise you might risk that you get dublets) - But i dont know your business logic.

When you load from your source with the maxid you only have the newest records. These you wanna concatenate with the old records from your QVD, and then you wanna store this into the QVD so you get all records.

[arulsettu]

thank u all