VALUES
Function
Computes the value of one or more rows based on a given value expression. It is commonly used to generate a constant table within a larger command.
Precautions
- Do not use VALUES to return a very large number of result rows, as it may lead to memory exhaustion or poor performance. For the INSERT INTO VALUES syntax, you are advised to use it in scenarios involving small amounts of data insertion. For inserting large volumes of data, consider using other import methods provided by the product.
- If multiple rows are specified, each row must contain the same number of elements.
Syntax
1 2 3 4 |
VALUES {( expression [, ...] )} [, ...] [ ORDER BY { sort_expression [ ASC | DESC | USING operator ] } [, ...] ] [ { [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] } | { LIMIT start, { count | ALL } } ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]; |
Parameter Description
- expression
A constant or expression used to compute or insert into a specific location in the result table.
In a VALUES list appearing at the top level of INSERT, expression can be replaced with DEFAULT to represent the default value of the destination column being inserted. However, DEFAULT cannot be used when VALUES appears in other contexts.
- sort_expression
An expression or integer constant indicating how to sort the resulting rows.
- ASC
Specifies sorting in ascending order.
- DESC
Specifies sorting in descending order.
- operator
A sorting operator.
- count
Maximum number of rows to return.
- start
Number of rows to skip before beginning to return rows.
- FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY
The FETCH clause limits the total number of rows returned from the first row of the query result, with the default value of count being 1.
Examples
Create the reason_t1 table.
1 2 3 4 5 6 |
CREATE TABLE reason_t1 ( TABLE_SK INTEGER , TABLE_ID VARCHAR(20) , TABLE_NA VARCHAR(20) ) store AS orc; |
Insert a record into the table.
1
|
INSERT INTO reason_t1(TABLE_SK, TABLE_ID, TABLE_NA) VALUES (1, 'S01', 'StudentA'); |
Insert a record into the table. The syntax is equivalent to the previous syntax.
1
|
INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA'); |
Insert records whose TABLE_SK is less than 1 into the table.
1
|
INSERT INTO reason_t1 SELECT * FROM reason_t1 WHERE TABLE_SK < 1; |
Insert multiple records into the table.
1 2 3 4 5 6 7 8 |
INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA'),(2, 'T01', 'TeacherA'),(3, 'T02', 'TeacherB'); SELECT * FROM reason_t1 ORDER BY 1; TABLE_SK | TABLE_ID | TABLE_NAME ----------+----------+------------ 1 | S01 | StudentA 2 | T01 | TeacherA 3 | T02 | TeacherB (3 rows) |
Use INSERT OVERWRITE to update data in a table, that is, insert data in overwrite mode.
1 2 3 4 5 6 |
INSERT OVERWRITE INTO reason_t1 values (4, 'S02', 'StudentB'); SELECT * FROM reason_t1 ORDER BY 1; TABLE_SK | TABLE_ID | TABLE_NAME ----------+----------+------------ 4 | S02 | StudentB (1 rows) |
Insert data back into the reason_t1 table.
INSERT INTO reason_t1 SELECT * FROM reason_t1;
Specify the default values for independent columns.
INSERT INTO reason_t1 VALUES (5, 'S03', DEFAULT);
To insert some data in a table to another table, perform the WITH subquery to obtain the temporary table temp_t, and then insert all data in the temporary table temp_t to the reason_t1 table.
WITH temp_t AS (SELECT * FROM reason_t1) INSERT INTO reason_t1 SELECT * FROM temp_t ORDER BY 1;
Insert data into a specified partition of a partitioned table.
1 2 3 4 5 |
CREATE TABLE test_value_row(a int)PARTITION BY (d int) store AS orc; INSERT OVERWRITE INTO test_value_row VALUES(55,51); INSERT OVERWRITE INTO test_value_row VALUES(85,80); ALTER TABLE test_value_row DROP PARTITIONS (d=51); |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot