Inserting Multiple Rows into Netezza Table

Standard

access-denied-715x400This one has been irking me for quite a bit. If you have to insert multiple rows into a table from a list or something, you may be tempted to use the standard PostgreSQL method of…

INSERT INTO TABLE_NAME VALUES
(1,2,3),
(4,5,6),
(7,8,9)
;

Be warned – THIS WILL NOT WORK IN NETEZZA! Netezza does not allow for the insertion of multiple rows in one statement if you are using VALUES.

You have two options here: 1) create a file with your values and load it (see Loading Data into Netezza post) or 2) use individual INSERT INTO statements. With a small, simple set of records like the one above, the second method will do fine. It would look like this…

INSERT INTO TABLE_NAME VALUES (1,2,3);
INSERT INTO TABLE_NAME VALUES (4,5,6);
INSERT INTO TABLE_NAME VALUES (7,8,9);

If you have larger sets of data to insert or more complex row structures, consider using an external table.

Official documentation for IBM Netezza INSERT command.

3 thoughts on “Inserting Multiple Rows into Netezza Table

  1. Daniel

    I needed to create a number of large tables (over 600M records) and the easiest and quickest approach for me was to just insert a seed record and then use iteratively
    Insert into t select … from t
    (A lot of my values were based on random())
    Its pretty efficient and you can get large number of records created quickly.

Leave a Reply

Your email address will not be published. Required fields are marked *