Insert multiple rows with INSERT INTO

Yeah, that won't work.

You could do this:

dbadmin=> CREATE LOCAL TEMPORARY TABLE "iris" ("Sepal.Length" DOUBLE PRECISION, "Sepal.Width" DOUBLE PRECISION, "Petal.Length" DOUBLE PRECISION, "Petal.Width" DOUBLE PRECISION, "Species" VARCHAR) ;
CREATE TABLE

dbadmin=> INSERT INTO "iris"
dbadmin->   SELECT 5.1, 3.5, 1.4, 0.2, 'setosa' UNION
dbadmin->   SELECT 4.9, 3.0, 1.4, 0.2, 'setosa' UNION
dbadmin->   SELECT 4.7, 3.2, 1.3, 0.2, 'setosa';
 OUTPUT
--------
      3
(1 row)

dbadmin=> SELECT * FROM "iris";
 Sepal.Length | Sepal.Width | Petal.Length | Petal.Width | Species
--------------+-------------+--------------+-------------+---------
          4.9 |           3 |          1.4 |         0.2 | setosa
          4.7 |         3.2 |          1.3 |         0.2 | setosa
          5.1 |         3.5 |          1.4 |         0.2 | setosa
(3 rows)

This works! Thanks. For context I would like to get dplyr (the R package) working with Vertica. I'd essentially like to update the vertica.dplyr package so that it works. However I am an intermediate R programmer and pretty new to Vertica so it is no small task. The solution you gave me seems like a slow way to load a dataset from R into Vertica. Is there a faster way to get a dataset into Vertica (without admin access) like COPY LOCAL?

I tried

COPY iris FROM LOCAL "C:/project/iris.csv"

but this does not seem to work.

The documentation here is a little hard for me to follow.

HI,

The COPY LOCAL command is a vsql command. You'll have to have vsql (Vertica's command line interface) installed locally where that csv file is located.

Are you trying to use vsql?

You can grab the Windows version here:
https://my.vertica.com/download/vertica/client-drivers/

I was thinking @ablack3, per his question...

Is there a faster way to get a dataset into Vertica (without admin access) like COPY LOCAL?

...was looking for the easiest method i.e. vsql COPY LOCAL

Offers the UNION option any advantages in terms of speed?

According the Vertica docs it seems this should be possible using the syntax provided by the OP, is this new since 2018?

INSERT INTO t1 VALUES (1,2, 'un, deux'), (3,4, 'trois, quatre');

https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/INSERT.htm#5

Yes... it is a new feature available starting 11.1.1

https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/NewFeatures/11.1/11.1.1/SQLandFunctions.htm

thanks, this is really useful. is there a way to browse new features added in the last month/year/decade, and conversely, tell what version introduced a feature?

You can see all new features added in the past and tell in which version each was introduced by browsing
the "New and Changed" paragraph in Vertica documentation.
See for example "New and Changed in Vertica 12.0.3" here:
https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/NewFeatures/12.0/12.0.3/NewFeaturesandChangesIn12.0.3.htm