Updated on 2024-08-19 GMT+08:00

INSERT INTO

This section describes how to use the INSERT INTO statement to write job results to a sink table.

Writing Data to a Sink Table

  • Syntax
    1
    2
      INSERT INTO your_sink
      SELECT ... FROM your_source WHERE ...
    
  • Example
    In this example, two tables my_source and my_sink are defined, and the INSERT INTO statement is used to select data from the source table and insert the data to the sink table.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    -- Use the datagen connector to create the source table my_source.
    CREATE TABLE my_source (
      name VARCHAR,
      age BIGINT
    ) WITH (
      'connector' = 'datagen');
    
    -- Use the JDBC connector to create the sink table my_sink.
    CREATE TABLE my_sink (
      name VARCHAR,
      age BIGINT
    ) WITH (
      'connector' = 'jdbc',
      'url' = 'jdbc:mysql://xxx/your-database',
      'table-name' = 'your-table',
      'username' = 'your-username',
      'password' = 'your-password'
    );
    
    -- Run the INSERT INTO statement to select data from the my_source table and insert the data into the my_sink table.
    INSERT INTO my_sink
    SELECT name, age
    FROM my_source;
    

Writing Data to Multiple Sink Tables

EXECUTE STATEMENT SET BEGIN... END; is a required statement for writing data to multiple sink tables. It is used to define multiple data insertion operations in the same job.

EXECUTE STATEMENT SET BEGIN... END; is required when data is written to multiple sink tables.

  • Syntax
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    EXECUTE STATEMENT SET BEGIN
    -- First DML statement
      INSERT INTO your_sink1
      SELECT ... FROM your_source WHERE ...;
    
    -- Second DML statement
      INSERT INTO your_sink2
      SELECT ... FROM your_source WHERE ...
    
    ...
    END;
    
  • Example
    In this example, the source table datagen_source and sink tables print_sinkA and print_sinkB are defined. EXECUTE STATEMENT is used to execute two INSERT INTO statements to write the converted data to two different sinks.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    -- Use the datagen connector to create the source table datagen_source.
    CREATE TABLE datagen_source (
      name VARCHAR,
      age BIGINT
    ) WITH (
      'connector' = 'datagen'
    );
    
    -- Use the print connector to create the result tables print_sinkA and print_sinkB.
    
    CREATE TABLE print_sinkA(
      name VARCHAR,
      age BIGINT
    ) WITH (
      'connector' = 'print' 
    );
    
    CREATE TABLE print_sinkB(
      name VARCHAR,
      age BIGINT
    ) WITH (
      'connector' = 'print' 
    );
    
    -- Use EXECUTE STATEMENT SET BEGIN to execute two INSERT INTO statements.
    -- The first INSERT INTO statement converts the data in the datagen_source table as needed and writes the converted data to print_sinkA.
    -- The second INSERT INTO statement converts data as needed and writes the converted data to print_sinkB.
    EXECUTE  STATEMENT SET BEGIN
    INSERT INTO print_sinkA 
      SELECT UPPER(name), min(age) 
      FROM datagen_source 
      GROUP BY UPPER(name);
    INSERT INTO print_sinkB 
      SELECT LOWER(name), max(age) 
      FROM datagen_source 
      GROUP BY LOWER(name);
    END;