Updated on 2025-04-21 GMT+08:00

INSERT

Function

This command inserts SELECT query results to a Delta table.

Syntax

Append mode:

INSERT INTO [database_name.]table_name|DELTA.`obs://bucket_name/tbl_path`

select query;

Overwrite mode:

INSERT OVERWRITE [database_name.]table_name|DELTA.`obs://bucket_name/tbl_path`

select query;

Parameter Description

Table 1 Parameter descriptions of INSERT INTO

Parameter

Description

database_name

Name of the database, consisting of letters, numbers, and underscores (_)

table_name

Name of the table in the database, consisting of letters, numbers, and underscores (_)

bucket_name

OBS bucket name

tbl_path

Storage location of the Delta table in the OBS bucket

select query

Query statement

Required Permissions

  • SQL permissions
Table 2 Permissions required for executing INSERT INTO

Permission Description

INSERT_INTO_TABLE permission on a table

  • Fine-grained permission: dli:table:insertIntoTable
  • Metadata services provided by LakeFormation. Refer to the LakeFormation documentation for details on permission configuration.

Example

insert into delta_table0 values(1, 'a1', 20);

insert into delta_table0 select 1, 'a1', 20;

insert into test_delta_parts1 PARTITION(dt) select id,name,dt from test_delta1;

-- insert overwrite table
insert overwrite table delta_table0 select 1, 'a1', 20;

System Response

Displays whether the task is successfully executed in the execution history or job list.