Vertica SQL insert multiple rows in one statement

sql vertica
ben890 picture ben890 · May 10, 2016 · Viewed 8.8k times · Source

Was wondering whether it's possible to accomplish the following in one insert statement?

drop table analytics.bw_covariance_matrix;
create table analytics.bw_covariance_matrix (
row int,
x1 float,
x2 float,
x3 float
);

insert into analytics.bw_covariance_matrix VALUES
(1, 4.01926965, -0.4686067, -0.07592112),
insert into analytics.bw_covariance_matrix VALUES
(2, -0.46860675,  4.1799267, -0.82461139);
insert into analytics.bw_covariance_matrix VALUES
(3, -0.07592112, -0.8246114,  4.37186211);

Answer

JNevill picture JNevill · May 10, 2016

You could UNION a SELECT to have a single INSERT statement:

insert into analytics.bw_covariance_matrix 
SELECT 1, 4.01926965, -0.4686067, -0.07592112
UNION
SELECT 2, -0.46860675,  4.1799267, -0.82461139
UNION
SELECT 3, -0.07592112, -0.8246114,  4.37186211

I don't believe that Vertica has a multi record insert statement like MySQL and other RDBMS's, so this is the best bet.

Related questions

SQL left outer join on multiple columns

According to this SQL join cheat-sheet, a left outer join on one column is the following : SELECT * FROM a LEFT JOIN b ON a.foo = b.foo WHERE b.foo IS NULL I'm wondering what it would look like with …

Read More
Export Vertica query result to csv file

I`m working with Vertica. I try to export data from SELECT query into csv. I tried making it with sql query: SELECT * FROM table_name INTO OUTFILE '/tmp/fileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"…

Read More
Redshift Divide By Zero Puzzler

I was getting a divide by 0 error with this code: CASE WHEN DENOMINATOR >= 0 THEN SUM(INT1 * INT2 / DENOMINATOR) ELSE 0 END AS RATIO However when I changed to the following code, it worked. CASE WHEN DENOMINATOR >= 0 THEN SUM(INT1) * …

Read More