Convert list of values in SQL to CTE or Temp Table

Use values:

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

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

Assuming you have a list of values (value1, value2,...value500) as a huge string.. you can first assign that to a variable and then use a split list kind of function and then pass the variable to the function.

This is how you could use it

declare @input varchar(max) = ('value1,value2,value3,value499,value500')
select * from dbo.SplitList (@input,',')

The function I use is this:

create FUNCTION [dbo].[SplitList](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (value nvarchar(4000))
AS
--this function takes two parameters; the first is the delimited string, the second is the delimiter

    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1

    IF @String IS NULL RETURN
    WHILE @INDEX !=0


        BEGIN    
            -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
            SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
            -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
            IF @INDEX !=0
                SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
            ELSE
                SELECT @SLICE = @STRING
            -- PUT THE ITEM INTO THE RESULTS SET
            INSERT INTO @Results(value) VALUES(@SLICE)
            -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
            SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
            -- BREAK OUT IF WE ARE DONE
            IF LEN(@STRING) = 0 BREAK
    END

    RETURN
END

This returns a table with all your values in it.