You can translate the question and the replies:

insert into a materialized view using select

Hi there, I have 2 questions: 1. Can you please provide a syntax for this example below. I want to add 2 more set of rows: Washington, WA, Seatle and Colorado, CO, Denver into this table 'usa_state' which has 3 fields: state, abbreviation, capital Right now i can only insert one by one row. **I tried multiple rows at once as below. It doesn't work and keeps telling me: Error parsing command ** INSERT INTO usa_state ( SELECT 'Washington', 'WA', 'Olympia' select 'Colorado', 'CO', 'Denver' ); **2. I also tried another syntax using Values as below:** INSERT INTO usa_state (state, abbreviation, capital) values ('Washington', 'WA', 'Seatle'); But this syntax results in error: **The user does not have INSERT privileges on the view 'usa_state'** Could you please help me on these 2 syntax? Table usa_state is created using this syntax below: CREATE OR REPLACE MATERIALIZED TABLE usa_state ( state : text , abbreviation : text , capital : text ) CONSTRAINT 'primary_key_usa_state' PRIMARY KEY ('state'); ; Thank you very much!
user
17-01-2024 00:29:55 -0500
code

3 Answers

Hi, For inserting multiple rows in a materialized table, I would use the query in below format: > INSERT INTO materialized_table ( fields ) VALUES (row1), (row2)..(rowN); For instance, if I need to add two rows ( 'Washington', 'WA', 'Olympia') and ( 'Colorado', 'CO', 'Denver'), I would execute the below command in the VQL shell. ``` INSERT INTO usa_state values ( 'Washington', 'WA', 'Olympia'), ( 'Colorado', 'CO', 'Denver'); ``` Or ``` INSERT INTO usa_state (state, abbreviation, capital) values ( 'Washington', 'WA', 'Olympia'), ( 'Colorado', 'CO', 'Denver'); ``` Also, if I face `The user does not have INSERT privileges on the view 'usa_state'` error, I would check if the user who is trying to insert rows in the 'usa_state' table has enough privileges over that table . For further information about inserting and modifying materialized tables, please refer [Materialized Tables](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/materialized_tables/creating_materialized_tables/creating_materialized_tables#inserting-data-into-materialized-tables) section of the Virtual DataPort VQL Guide and to know more about the users and the necessary privileges for a user, take a look at the [User and access rights](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/databases_users_and_access_rights_in_virtual_dataport/user_and_access_right_in_virtual_dataport/user_and_access_right_in_virtual_dataport#insert-update-and-delete-privileges) section of the Virtual DataPort Administration Guide . Hope this helps!
Denodo Team
 Edited on: 17-01-2024 05:13:30 -0500
code
Hi there, can you please provide the syntax which uses 'Select' like this one: INSERT INTO usa_state ( SELECT 'Arizona', 'AZ', 'Phoenix' ); But for multiple rows. As it said in the link that the benefit of using 'Select' clause over using 'Values' is that you can insert several rows at once. I hope this clarifies my concern a bit
user
17-01-2024 22:38:06 -0500
Hi, In general, the select clause within the insert statement is used to insert the result of the select statement in the materialized table. The syntax of this type of query is as follows: ``` INSERT INTO materialized table ( SELECT clause ) ``` For example, if I have to insert the results of a table ‘usa_state_data’ into the ‘usa_state’ table, then I would use the below query. ``` INSERT INTO usa_state ( SELECT * from usa_state_data); ``` The above query helps in inserting the result of the query ‘SELECT * from usa_state_data’ into the ‘usa_state’ table. The result of the select query should `have the same schema as the materialized table` and in the result, the fields must be in the same order as in the target table. By this way, this option is useful in inserting multiple rows with one select statement. If I need to insert multiple row data like ( 'Arizona', 'AZ', 'Phoenix'),( 'Washington', 'WA', 'Olympia') manually at once, then it is feasible to use the below syntax: ``` INSERT INTO materialized_table ( fields ) VALUES (row1), (row2)..(rowN); ``` Hope this helps!
Denodo Team
18-01-2024 03:20:01 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here