The Netezza COPY command moves data between IBM Netezza tables and standard file system files, sometimes to standard output.
Netezza COPY Command
Use the COPY command with a file name to read directly from or write to a file. The nzload command is much faster and stable compared to COPY command. Netezza always recommends using nzload instead of COPY command.
Netezza does not recommend using the COPY command. For load/unload operations use the nzload command or CREATE EXTERNAL TABLE commands. These commands are faster and more stable than the COPY command. There is no COPY command in SQL 92.
Read:
- guide to load data into Netezza
- Netezza External Tables and Examples
- nzload Command and its Usage in Netezza
Netezza COPY Command Syntax
Syntax for input coming from a client application:
COPY [BINARY] table FROM { 'file name' | stdin } [ [USING] DELIMITERS 'delimiter' ] [ WITH NULL AS 'null string' ];
Syntax for output going to a client application:
COPY table TO { 'file name' | stdout } [ [USING] DELIMITERS 'delimiter' ] [ WITH NULL AS 'null string' ];
Netezza COPY Command Syntax Options
Below are the some of the opotions that are used along with the COPY command:
| Option | Description |
| BINARY | Changes the behavior of field formatting. Forces all data to be stored or
read in binary format rather than text. |
| delimiter | Specifies the character that separates fields within each row of the file. |
| file name | Specifies the absolute path name of the input or output file. |
| null string | The string that represents a NULL value. The default is “\N”. |
| stdin | Specifies that input comes from the client application. |
| stdout | Specifies that output goes to the client application. |
| table | Specifies the name of an existing table. |
Netezza COPY Command Examples
UseCOPY Command to load data from stdin
Below is the examples to load data from stdin:
TRAINING.ADMIN(ADMIN)=> COPY tab1 FROM stdin USING DELIMITERS ','; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1,'abc' >> 2,'bcd' >> 3,'def' >> \. TRAINING.ADMIN(ADMIN)=> select * from tab1; COL1 | COL2 ------+------------ 1 | 'abc' 3 | 'def' 2 | 'bcd' (3 rows) TRAINING.ADMIN(ADMIN)=>
Netezza COPY Command to load data from file
Below is the command to load table from file:
TRAINING.ADMIN(ADMIN)=> COPY tab1 FROM '/export/home/nz/test.dat' USING DELIMITERS ','; COPY 3 TRAINING.ADMIN(ADMIN)=>
Netezza COPY Command to unload data to file
TRAINING.ADMIN(ADMIN)=> COPY tab1 TO '/export/home/nz/test.out' USING DELIMITERS ','; COPY 3 TRAINING.ADMIN(ADMIN)=>