INSERT INTO

Updated on 2024-09-29 GMT+08:00

View PDF

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

      
           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.

       
        
          
          -- 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.

CAUTION:

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

Syntax

      
         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.

       
        
          
          -- 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;

         

       
      

Parent topic: DML Snytax

Previous topic: SELECT

Next topic: Set Operations

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot