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?
3 Answers
Use values:
WITH huge_list_cte AS (
SELECT v
FROM (VALUES (value1), (value2), . . . ) v(v)
)
. . .
5 Comments
Laughing Vergil
Only works if the number of entries in the values list is <= 1000.
AnotherDeveloper
Can I do this without wrapping each value in round brackets?
Gordon Linoff
@AnotherDeveloper . . . The parentheses are needed for the syntax.
AnotherDeveloper
@GordonLinoff Got it thanks. My hope was that if the SQL engine can interpret (a,b,c) as a list of values, there would be a way to take (a,b,c) and either select directly from it, or convert it to another data structure.
Gordon Linoff
@AnotherDeveloper . . . Lists are not a type in SQL. They are really only part of
IN expressions. (Many databases would interpret that construct as a tuple not a list.)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
Comments
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.
insert into #temp (field_name) values (value1)statements? What do you want to do with the values once you have them in a database? Your answer will help shape the responses (CTE vs Temp Table).string_splitin SQL 2016) which takes a string and splits it into a table based on separator values. Most of these allow the output to be used directly as a table.INSERT INTO ... Values(..),(...),(...)...statement (NOTE: Must be split into a maximum of 1000 rows per insert statement).