insert_into_by_name.sql

-- Base table: raw ice cream sales
CREATE TABLE icecream_sales (
                                sale_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                                flavor      VARCHAR2(50) NOT NULL,
                                sale_date   DATE NOT NULL,
                                scoops_sold NUMBER NOT NULL
);

-- Insert some sample data
INSERT INTO icecream_sales (flavor, sale_date, scoops_sold) VALUES ('Vanilla', DATE '2024-07-01', 120);
INSERT INTO icecream_sales (flavor, sale_date, scoops_sold) VALUES ('Chocolate', DATE '2024-07-01', 200);
INSERT INTO icecream_sales (flavor, sale_date, scoops_sold) VALUES ('Strawberry', DATE '2024-07-02', 150);
INSERT INTO icecream_sales (flavor, sale_date, scoops_sold) VALUES ('Vanilla', DATE '2025-06-15', 180);
INSERT INTO icecream_sales (flavor, sale_date, scoops_sold) VALUES ('Chocolate', DATE '2025-06-15', 220);
INSERT INTO icecream_sales (flavor, sale_date, scoops_sold) VALUES ('Mint', DATE '2025-06-20', 95);

CREATE TABLE icecream_sales_summary (
                                        flavor       VARCHAR2(50),
                                        sales_year   NUMBER,
                                        total_sales  NUMBER
);

-- Insert summary rows with INSERT BY NAME
INSERT INTO icecream_sales_summary
    BY NAME SELECT
                flavor,
                EXTRACT(YEAR FROM sale_date) as sales_year,
                SUM(scoops_sold) AS total_sales
            FROM icecream_sales
            GROUP BY ALL;

-- Variant syntax with explicit column names defined at the beginning of the query
INSERT INTO icecream_sales_summary
(flavor, sales_year, total_sales)
    BY NAME SELECT
                flavor,
                EXTRACT(YEAR FROM sale_date) as sales_year,
                SUM(scoops_sold) AS total_sales
            FROM icecream_sales
            GROUP BY ALL;