Promo Center

50% off for new user

Direct Mail-46% off

Learn More

INSERT INTO statement

Updated at: 2024-09-03 10:25

This topic describes how to use the INSERT INTO statement to write data to one or more sinks in a deployment.

Background information

The INSERT statement allows you to use the OPTIONS hints to pass parameters to a result table. For more information, see SQL Hints.

Example on how to write data to a sink

-- Create a source table.
CREATE TEMPORARY TABLE datagen_source (
  name VARCHAR,
  score BIGINT
) WITH (
  'connector' = 'datagen' 
);

-- Create a result table.
CREATE TEMPORARY TABLE blackhole_sink(
  name VARCHAR,
  score BIGINT
) WITH (
  'connector' = 'blackhole' 
);

--DML
INSERT INTO blackhole_sink SELECT UPPER(name), score FROM datagen_source;

Example on how to write data to multiple sinks

Important

The statement that writes data to multiple sinks must start with BEGIN STATEMENT SET; and end with END;.

In this example, data is written to two sinks.

-- Create a source table.
CREATE TEMPORARY TABLE datagen_source (
  name VARCHAR,
  score BIGINT
) WITH (
  'connector' = 'datagen'
);

-- Create Result Table A.
CREATE TEMPORARY TABLE blackhole_sinkA(
  name VARCHAR,
  score BIGINT
) WITH (
  'connector' = 'blackhole' 
);

-- Create Result Table B.
CREATE TEMPORARY TABLE blackhole_sinkB(
  name VARCHAR,
  score BIGINT
) WITH (
  'connector' = 'blackhole' 
);

--DML
BEGIN STATEMENT SET;      -- This is required when you write data to multiple sinks. 
INSERT INTO blackhole_sinkA 
  SELECT UPPER(name), sum(score) 
  FROM datagen_source 
  GROUP BY UPPER(name);
INSERT INTO blackhole_sinkB 
  SELECT LOWER(name), max(score) 
  FROM datagen_source 
  GROUP BY LOWER(name);
END;      -- This is required when you write data to multiple sinks.
  • On this page (1, T)
  • Background information
  • Example on how to write data to a sink
  • Example on how to write data to multiple sinks
Feedback
phone Contact Us

Chat now with Alibaba Cloud Customer Service to assist you in finding the right products and services to meet your needs.

alicare alicarealicarealicare