Build a list of defined values into CTE

Question

EDIT: previously advised solution works only for MSSQL. Therefore I am adding an Oracle solution. I am keeping the original answer below.

I thought of one more solution (though the one provided by Justin Cave still seems a bit better) – using temporary tables.

Here is how it may look like

CREATE GLOBAL TEMPORARY TABLE temp_ids
   (id INT)
   ON COMMIT PRESERVE ROWS;

INSERT INTO ids (id) VALUES (101);
INSERT INTO ids (id) VALUES (102);
INSERT INTO ids (id) VALUES (103);

This should be a valid solution for Oracle database.

Original answer below

I have come across similar issue and here is my solution (this does not work on Oracle DB as mentioned in comments, only MSSQL though)

WITH cte AS (
    SELECT * FROM (
        VALUES
            (1, 2, 3, ...),
            (2, 3, 4, ...)
        ) AS a (col1, col2, col3, ...)
    )
INSERT INTO ...

Hope this helps 🙂

Leave a Comment Cancel reply