This 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.
INSERT INTO TABLE_NAME
select 1,2,3 union all
select 4,5,6 union all
select 7,8,9;
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.
Yes, this is a much more efficient method if you already have the data in some other table or it is being generated in-database.
Why you so stupid, Netezza!?