Incremental Load by using Primary key (Insert & Update Method)

[Rsaiq]

Hi All,

I need to perform increment load by using primary key.

I have 2 tables as below

Table1:

Id    Name

1     jhon

2      Sam

Table2:

1     Carry

3     Hales

Now required output should be like below

1     Carry-----Updated record

2      Sam

3      Hales------newly inserted record

I followed below script  but its not giving expected result:

ExistingData:
LOAD
ID,
Name

FROM [lib://AttachedFiles/ExistingData.qvd]
(qvd);

MaxIDTable:
Load Max(ID) as MaxID Resident ExistingData;

Let MaxID=peek('MaxID', 0, 'MaxIDTable');

 Drop table ExistingData;

Incremental_Load:
LOAD
ID, Name
FROM [lib://AttachedFiles/Data.xlsx] (ooxml, embedded labels, table is QVD2) where ID> $(MaxID);

Concatenate
LOAD
ID,
Name
FROM [lib://AttachedFiles/ExistingData.qvd] (qvd)where not Exists(ID);


STORE Incremental Load INTO [lib://AttachedFiles/ExistingData.qvd];


Drop Table Incremental Load;

Drop Table MaxIDTable;

Data:
LOAD
ID, Name
FROM  [lib://AttachedFiles/ExistingData.qvd] 

Could anyone please help me to get this resolved using insert & updated method.

Thanks in advance

[Gabbar]

Is there any created On or updated on in the table?,
because without that updating would be difficult.

[Rsaiq]

Hi @Gabbar ,

I have just updated script in the post which I followed.

We need to update data in table1 by adding new records and updating the existing data.

[Gabbar]

The main case here is updating the existing data, Without time field it wont work properly, If you have a time field please tell, 
Otherwise the best case is to just save the new data directly rather than using incremental load.

[Rsaiq]

Hi @Gabbar 

Point no.1 ---We don't have time field that's why it's a challenge for us.

Point no.2---This is just a sample data so it will load in seconds !!!!

But in real scenario loading time taking like 2-3 hours.

Thanks

[Gabbar]

This method is slow, but as you said it takes 2 hours  so it might work:

While in extraction create a new column which is combination of all fields like in case case as 
ID&Name&(Other Fields) as Table_Key.

Now also Create the same field in already existing table and then do the inner join of the existing table and this key like:
Existing:
Load ID,Name, ID&Name as Key from Existing_Table:
inner join
Load Key from New_Table;(Only Load Single Field here for faster loading)

Concatenate
incremental_Table:
Load * from New_Table where not exists(ID);

This Will only Work when you have large amount of data compared to amount of new data 
and large amount of new data compared to updated data.

[Rsaiq]

Hi @Gabbar 

Could you please provide full script as I am not getting it.Thanks

[Gabbar]

Existing:
LOAD
Id,
Name,
Id&Name as Key //Didnt Store in previous store Script;
FROM [lib://DataFiles/Incremental1.qvd]
(qvd);
Inner Join(Existing)
Joining_Table:
Load Id&Name as Key; /// just 1 field /////
/// from ///
/// Next 5 line represent data from source like DB or excel/////
Load * Inline [
Id,Name
1,Charles
2,Sam
3,Harry
];

Concatenate(Existing)
New:
Load Id,Name,Id&Name as Key /// all the fields ////
where not Exists(Id);
/// From ///
/// Next 5 line represent data from source like DB or excel/////
Load * Inline [
Id,Name
1,Charles
2,Sam
3,Harry
];

This is sample then you can store final table as required.

[Rsaiq]

Hi @Gabbar 

I tried with above script but unfortunately it's giving me only new records like below

ID   Name

1     Carry

3     Hales

My output should be old record + updated record + New record like below

1 Carry-----Updated record

2 Sam------Old Record

3 Hales------new record

Thanks 

[Gabbar]

Can you debug it  and tell me where is that Old record is missing because the inner join table should generate old record and it is doing so for me.