Insert multiple rows with INSERT INTO

ablack3

Is it possible to insert multiple rows into a Vertica database table with a single SQL command?

I'm looking for something like this.

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

        INSERT INTO "iris" VALUES (5.1, 3.5, 1.4, 0.2, 'setosa'),
        (4.9, 3.0, 1.4, 0.2, 'setosa'), 
        (4.7, 3.2, 1.3, 0.2, 'setosa)

...
ect.

This produces an error. The documentation states "You must insert one complete tuple at a time." Does this mean that I need one INSERT INTO SQL statement for each row of data?

Thanks!

Jim_Knicely

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)

ablack3

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.

Jim_Knicely

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/

Dingqiang

copy... from local... should work any Vertica client including vsql/ODBC/JDBC/ADO.NET, so it also works through rodbc.

Jim_Knicely

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

joergschaber

Offers the UNION option any advantages in terms of speed?

Pieter_Sheth-Vo

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

SruthiA

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

Pieter_Sheth-Vo

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?

mosheg

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