You can insert new records and update existing by using nzload command itself. In this article, we will discuss about the new feature introduced in nzload. Netezza version 7.2.x supports the Netezza nzload merge operations.
Netezza nzload merge operations
The Netezza nzload command now supports merge operations to enable this new feature you must use three new options in nzload:
- -merge option—Specifies whether to merge the contents of the data file into the target table by inserting rows, updating rows, or both
- INSERT – Inserts new rows into the table.
- UPDATE – Updates existing rows in the table.
- INSERTUPDATE – Inserts new rows into the table and updates existing rows in the table.
- -mergeSchema option—Specifies the schema of the target table and, optionally, a match condition for the -merge option.
- -mergeOn option—Specifies a match condition, a filter, or both for the -merge option. By default, -mergeon condition will be NULL.
Read:
- Netezza Fixed-Width Data loading and Examples
- Netezza nzload Control file and Syntax with an Examples
- Guide to Load data into Netezza Database
- nzload command and its usage
- nzsql command and its usage
- Netezza Export Table Data to CSV Format
- IBM Netezza Unload Table using External Tables and Examples
Netezza nzload merge operations Examples
Below example illustrates the new Netezza nzload merge capabilities:
[nz@netezza ~]$ nzsql -db training -c 'select * from PATIENT_1 order by 1'; ID | NAME ----+------ 1 | ABC 2 | BCD 3 | CDE 4 | DEF 5 | EFG (5 rows) [nz@netezza ~]$ cat test.dat 1|ABd 2|BCa 3|CDr 6|AAA 7|BBB [nz@netezza ~]$ nzload -db training -t PATIENT_1 -df test.dat -delim '|' -merge INSERTUPDATE -mergeschema "ID int primary_key,NAME char(3)" Load session of table 'PATIENT_1' completed successfully [nz@netezza ~]$ [nz@netezza ~]$ nzsql -db training -c "select * from patient_1 order by 1" ID | NAME ----+------ 1 | ABd 2 | BCa 3 | CDr 4 | DEF 5 | EFG 6 | AAA 7 | BBB (7 rows)