How to convert list of numbers into (temp) table using SQL (SQL Server)

Question

Here is an example;

I have list of numbers (1,5,8,36) and I want these values as a (temp) table rows. One of the way to do is as follow

select 1 as n into ##temp
union
select 5  as n 
union 
select 8 as n
union 
select 36 as n

The problem is number list is dynamic . it can have any no of values. So I need a proper systematic way to convert these values into temp table rows.

I'm looking for something better than N number of UNION in the SQLs — Bala, May 16 '12 at 08:23
UDF to split a string and return a table - http://stackoverflow.com/a/2657/1016183 — Morphed, May 16 '12 at 08:24
What is the source of these numbers? Can your application pass in a TVP instead? — Martin Smith, May 16 '12 at 08:24
The numbers are given by SP input? Are they always four numbers? — aF., May 16 '12 at 08:28
@aF. - `The problem is number list is dynamic . it can have any no of values.` — MatBailie, May 16 '12 at 08:33

score 6 · Accepted Answer · answered May 16 '12 at 08:33

A solution I use alot...

Supply your list of numbers as a VARCHAR(MAX) comma delimeted string, then use one of the many dbo.fn_split() functions that people have written on line.

One of many examples online... SQL-User-Defined-Function-to-Parse-a-Delimited-Str

These functions take a string as a parameter, and return a table.

Then you can do things like...

INSERT INTO @temp SELECT * FROM dbo.split(@myList)

SELECT
  *
FROM
  myTable
INNER JOIN
  dbo.split(@myList) AS list
    ON list.id = myTable.id

An alternative is to look into Table Valued Parameters. These allow you to pass a whole table in to a stored procedure as a parameter. How depends on the framework you're using. Are you in .NET, Java, Ruby, etc, and how are you communicating with the database?

Once we know more details about your applicaiton code we can show you both the client code, and the SQL stored procedure template, for using Table Valued Parameters.

SELECT * FROM SELECT $ids[]$ AS id_val x LEFT JOIN table1 t1 ON id_val = t1.id_val WHERE t1.id_val IS NULL — Bala, May 16 '12 at 10:40
I sure this function will work . But I'm hesitant to use ~100 lines function to create temp table. Is there any-other simpler way ? — Bala, May 16 '12 at 11:32
@Dexter - There are many split functions all over the internet, and many on Stack Overflow. Just search through them for one you like, and/or until you understand them and can modify them to your needs. — MatBailie, May 16 '12 at 22:08

score 1 · Answer 2 · answered May 21 '12 at 22:59

You Can Use Below Query For Select 100 Random Value From 1 To 9

Declare @Index Int = 1
Declare @Result Table (Col Int)
While @Index <= 100 Begin
    Insert Into @Result (Col)
    Select FLOOR( RAND() * 10)

    Set @Index = @Index + 1 
End

Select * From @Result

score 0 · Answer 3 · answered Oct 30 '15 at 17:41

0

I use this for a generic set of numbered rows.

SELECT DISTINCT ORDINAL_POSITION AS NUMBER_VAL 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ORDINAL_POSITION BETWEEN 1 AND 36
ORDER BY ORDINAL_POSITION

answered Oct 30 '15 at 17:41

HoggZilla

1
1

score 0 · Answer 4 · answered Feb 21 '19 at 22:10

    create temporary table NS AS (
    SELECT a+b+c+d+e+f+g+h 
    FROM       (SELECT 0 as a UNION SELECT 1) 
    CROSS JOIN (SELECT 0 as b UNION SELECT 2) 
    CROSS JOIN (SELECT 0 as c UNION SELECT 4)
    CROSS JOIN (SELECT 0 as d UNION SELECT 8)
    CROSS JOIN (SELECT 0 as e UNION SELECT 16)
    CROSS JOIN (SELECT 0 as f UNION SELECT 32)
    CROSS JOIN (SELECT 0 as g UNION SELECT 64)
    CROSS JOIN (SELECT 0 as h UNION SELECT 128)
    WHERE a+b+c+d+e+f+g+h BETWEEN 1 AND 200
    ORDER BY 1
)

Hi, please consider adding an explanation as to why this works/should be used instead of just a query. — ItsPete, Feb 21 '19 at 22:30

How to convert list of numbers into (temp) table using SQL (SQL Server)

4 Answers4

Linked