Often, a table needs to be refreshed by the contents of a file, in which many rows already exist and only a few are new.
A normal DATALOAD statement would reject all rows having a key value that already exists in the table; a normal DATAUPDATE statement would miss the rows which, up to this moment, are only available in the file.
This situation can be resolved by using the mixed form DATALOAD with DUPLICATES clause.
This statement consists of the following parts:
1. A DATALOAD statement, with table condition, if necessary
2. One of three possible DUPLICATES clauses (also see the Reference manual, INSERT)
3. The body of the DATALOAD statement specifying the columns
4. The INFILE specification
Example 1:
DATALOAD TABLE customer
UPDATE DUPLICATES
KEY cno 1-4
city 6-25
street 27-46
telephone 48-60
INFILE address.list
Example 2:
DATALOAD TABLE birthdaycalendar
IGNORE DUPLICATES
KEY name 1-30
birthday 31-40
day_in_year 41-43
INFILE staff.catalog DATE 'EUR'
In the first example, the addresses of available customers are updated and data related to new customers is entered into the table.
In the second example, the birthdays of the colleagues already inserted cannot change. Therefore, it is sufficient to add the birthdays of new colleagues.
The clause REJECT DUPLICATES can also be used. The effect of such a statement is identical to that of a simple DATALOAD statement.