Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Insert multiple rows with INSERT INTO

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

  • Offers the UNION option any advantages in terms of speed?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.