Incrementing an existing table with new rows from a second table in DataLoad

[Gaël]

Hi everyone,

I want to present local, detailed data together with EU-wide data. So I need to add the missing local measurement sites to the big list. Let's call the local data LU_sites and the bigger, external and non-modifiable list EU_sites. Both datasets do have lines in common that must not be repeated.

My current idea is to compute as LU_sites left join EU_sites to find the missing lines, then concatenate the result (New_LU_Sites) with EU_sites. The example below does not work. I would expect a Sites table with 7 rows (DE001, FR001, HR001, IT001, LU001, LU002, LU003). Where could be my error? Or how would you solve this?

LU_sites:
LOAD *
INLINE
[
SiteID, Site, Lat, Lon
LU001, Luxembourg, 1.21, 1.01
LU002, Esch, 1.22, 1.02
LU003, Strassen, 1.23, 1.03
];

// introducing a dummy to identify existing lines
EU_sites:
NOCONCATENATE
LOAD
1 as dummy, *;
LOAD *
INLINE
[
SiteID, Site, Lat, Lon
DE001, Frankfurt, 1.2, 1.0
FR001, Marseille, 1.2, 1.0
HR001, Dubrovnik, 1.2, 1.0
IT001, Roma, 1.2, 1.0
LU001, Luxembourg, 1.2, 1.0
];

New_LU_Sites:
LOAD *
RESIDENT LU_sites;
LEFT JOIN
LOAD *
RESIDENT EU_Sites
WHERE IsNull(dummy);

Sites:
LOAD Site, Lat, Lon;
LOAD * RESIDENT EU_sites;
CONCATENATE
LOAD * RESIDENT New_LU_Sites;

DROP TABLE LU_sites;
DROP TABLE EU_sites;
DROP TABLE New_LU_Sites;

[Chanty4u]

Update script 

New_LU_Sites:

NOCONCATENATE

LOAD *

RESIDENT LU_sites

WHERE NOT EXISTS(SiteID);

Sites:

NOCONCATENATE

LOAD SiteID, Site, Lat, Lon

RESIDENT EU_sites;

CONCATENATE

LOAD SiteID, Site, Lat, Lon

RESIDENT New_LU_Sites;

[Gaël · Accepted solution]

Thanks, @Chanty4u !

Your proposal RESIDENT LU_sites WHERE NOT EXISTS(SiteID); would give an empty dataset, but it lead to my first solution: 

LEFT JOIN(LU_sites)
LOAD SiteID, dummy
RESIDENT EU_sites;

New_LU_Sites:
NOCONCATENATE
LOAD *
RESIDENT LU_sites
WHERE IsNull(dummy);

Sites:
LOAD SiteID, Site, Lat, Lon
RESIDENT EU_sites;
CONCATENATE
LOAD SiteID, Site, Lat, Lon
RESIDENT New_LU_Sites;