Syntax for importing table

Question

What is the syntax for importing an external table into a created table? Whenever I right click on a table in squirrelsql, and click import file, I always get the error message "A database error has occurred. I suspect that Netzilla does not support file imports. How do I use the NZload utility to import the table?

Answer 1

Here's an example on how to load external data using jdbc. Most of these commands can also be run in the nzsql client. In your case just make sure your table definition for your table and the external file is in the same order, to simplify things.

Your real table in Netezza:

LABDB(ADMIN)=> CREATE TABLE REAL_TABLE (c1 int, c2 char(1), c3 char(1));
CREATE TABLE
Elapsed time: 0m0.047s

Your file to import: c:\test.file, with content:

1|a|x
2|b|z

Create your external table:

LABDB(ADMIN)=> CREATE EXTERNAL TABLE EXT_TEST SAMEAS REAL_TABLE USING (DATAOBJECT('C:\test.file') REMOTESOURCE 'JDBC');
CREATE EXTERNAL TABLE
Elapsed time: 0m0.015s

Check the data in your external table, using your squirrelsql or similar:

Displaying empty real_table:

LABDB(ADMIN)=> SELECT * FROM real_table;
 C1 | C2 | C3
----+----+----
(0 rows)

Elapsed time: 0m0.063s

Now you can insert into your real table from the external table, using SquirrelSQL or similar: INSERT INTO REAL_TABLE SELECT * FROM EXT_TEST;

Finally confirm the data in the real_table:

LABDB(ADMIN)=> SELECT * FROM real_table;
 C1 | C2 | C3
----+----+----
  2 | b  | z
  1 | a  | x
(2 rows)

Elapsed time: 0m0.074s

And drop the external table, this will not remove the file you used to load.

LABDB(ADMIN)=> DROP TABLE EXT_TEST;
DROP TABLE
Elapsed time: 0m0.018s