Help Center/ Data Lake Insight/ SQL Syntax Reference (ME-Abu Dhabi Region)/ Spark SQL Syntax Reference/ Creating a Datasource Connection with a DWS table/ Inserting Data to the DWS Table
Updated on 2023-03-06 GMT+08:00
View PDF
Share

Inserting Data to the DWS Table

Function

This statement is used to insert data in a table to the associated DWS table.

Syntax

  • Insert the SELECT query result into a table. 
    1
2
3
4
5
6
7
    		 
    INSERT INTO DLI_TABLE
  SELECT field1,field2...
  [FROM DLI_TEST]
  [WHERE where_condition]
  [LIMIT num]
  [GROUP BY field]
  [ORDER BY field] ...;
  • Insert a data record into a table. 
    1
2
    		 
    INSERT INTO DLI_TABLE
  VALUES values_row [, values_row ...];

Keywords

For details about the SELECT keywords, see Basic SELECT Statements.

Parameter description

Table 1 Parameter description

Parameter

Description

DLI_TABLE

Name of the table for which a datasource connection has been created.

DLI_TEST

indicates the table that contains the data to be queried.

field1,field2..., field

Column values in the DLI_TEST table must match the column values and types in the DLI_TABLE table.

where_condition

Query condition.

num

Limit the query result. The num parameter supports only the INT type.

values_row

Value to be inserted to a table. Use commas (,) to separate columns.

Precautions

  • The target table must exist.
  • When creating the table, you do not need to specify the Schema information. The Schema information complies with that in the DWS table. If the number and type of fields selected in the SELECT clause do not match the Schema information in the DWS table, the system reports an error.
  • You are advised not to concurrently insert data into a table. If you concurrently insert data into a table, there is a possibility that conflicts occur, leading to failed data insertion.

Example

  • Query data in the user table and insert the data into the test table. 
    1
2
3
4
5
6
    		 
    INSERT INTO test
  SELECT ATTR_EXPR
  FROM user
  WHERE user_name='cyz'
  LIMIT 3
  GROUP BY user_age
  • Insert data 1 into the test table. 
    INSERT INTO test 
  VALUES (1);