Insert multiple rows with INSERT INTO

edited March 2023 in Archived Posts

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!

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)
    
  • edited March 2018

    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_KnicelyJim_Knicely - Select Field - Administrator
    edited March 2018

    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/

  • copy... from local... should work any Vertica client including vsql/ODBC/JDBC/ADO.NET, so it also works through rodbc.
  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited March 2018

    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 :)

  • joergschaberjoergschaber Vertica Customer

    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

  • SruthiASruthiA Vertica Employee Administrator
  • 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?

  • moshegmosheg Vertica Employee Administrator
    edited February 2023

    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

This discussion has been closed.