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;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.