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.

Answer 1

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.

Answer 2

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