CODEWITHSUNDEEP
sqlvertica
Dschoni
Dschoni

Reputation: 3862

VERTICA insert multiple rows in one statement with named columns

I want to insert multiple rows efficiently into VERTICA. In PostgreSQL (and probably other SQL implementations) it is possible to INSERT multiple rows in one statement, which is a lot faster, than doing single inserts (especially when in Autocommit mode).

A minimal self-contained example to load two rows in a newly created table could look like this (a):

CREATE TABLE my_schema.my_table (
row_count int,
some_float float,
some_string varchar(8));

INSERT INTO my_schema.my_table (row_count, some_float, some_string) 
VALUES (1,1.0,'foo'),(2,2.0,'bar');

But the beauty of this is, that the order in which the values are bunched can be changed to be something like (b):

INSERT INTO my_schema.my_table (some_float, some_string, row_count) 
VALUES (1.0,'foo',1),(2.0,'bar',2);

Furthermore, this syntax allows for leaving out columns which are then filled by default values (such as auto incrementing integers etc.).

However, VERTICA does not seem to have the possibility to do a multi-row insert with the same fine-tuning. In fact, the only way to emulate a similar behaviour seems to be to UNION several selects together for something like (c):

INSERT INTO my_schema.my_table SELECT 1,1.0,'foo' UNION SELECT 2,2.0,'bar';

as in this answer: Vertica SQL insert multiple rows in one statement .

However, this seems to be working only, when the order of the inserted columns matches the order of their initial definition. My question is, it is possible to craft a single insert like (c) but with the possibility of changing column order as in (b)? Or am I tackling the problem completely wrong? If so, what alternative is there to a multi-row insert? Should I try COPY LOCAL?

Upvotes: 2

Views: 1708

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Just list the columns in the insert:

INSERT INTO my_schema.my_table (row_count, some_float, some_string)
    SELECT 1,1.0,'foo'
    UNION ALL
    SELECT 2,2.0,'bar';

Note the use of UNION ALL instead of UNION. UNION incurs overhead for removing duplicates, which is not needed.

Upvotes: 2

Related Questions