Convert list of values in SQL to CTE or Temp Table

Question

How can I convert a list of values like (value1, value2, value3, ...., value500) to a temp table or cte?

One way would be to do:

WITH huge_list_cte AS (
   Select value1
   UNION
   Select value2 ...
)

Is there a better way?

Answer 1

Use values:

WITH huge_list_cte AS (
      SELECT v
      FROM (VALUES (value1), (value2), . . . ) v(v)
     )
. . .

Answer 2

You can use table variable

DECLARE @Data TABLE(Id INT);
INSERT INTO @Data VALUES (101), (102) ...

Then you can use it in your queries as normal table

SELECT * FROM @Data

You can even create predefined table type and reuse it Microsoft Docs: table