Netezza nzload merge Operations

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

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 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)