Inserting Data Into Materialized Tables¶
There are two ways of inserting data into a materialized view:
Syntax 1
INSERT INTO <materialized table> ( <fields> )
VALUES (<values> ) [, ( <values> ) ]*
Inserts one or more rows into a materialized table.
This statement supports the same options as the one available for base and derived views (see Syntax of the INSERT statement) with the addition than you can insert several rows within the same statement. For example:
Example of inserting several rows in materialized table using a single INSERT statement¶
INSERT INTO usa_state (name, abbreviation, capital) VALUES
('Arizona', 'AZ', 'Phoenix')
, ('California', 'CA', 'Sacramento')
, ('New York', 'NY', 'Albany')
, ('North Dakota', 'ND', 'Bismarck');
Syntax 2
INSERT INTO <materialized table> ( <SELECT clause> )
Inserts the result of a SELECT
clause into a materialized table. The benefit
over the previous syntax is that with this one, you can insert
several rows at once.
The result of the query has to 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.
Example of the statement
INSERT... INTO
¶INSERT INTO usa_state (
SELECT 'Arizona', 'AZ', 'Phoenix'
);