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

Question

Here is an example:

I have a list of numbers (1,5,8,36), and I want these values ​​to be strings (temp). One way to do it is as follows

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

The problem is that the list of numbers is dynamic. it can have any meaning. Therefore, I need the right systematic way to convert these values ​​to rows in the temp table.

Answer 1

The solution I use alot ...

Put your list of numbers as a VARCHAR(MAX) string with a comma, then use one of the many dbo.fn_split() functions that people wrote on the line.

One of many online examples ... 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 view table values. They allow you to pass the entire table to the stored procedure as a parameter. How it depends on the structure used. You work in .NET, Java, Ruby, etc., and how do you communicate with the database?

As soon as we learn more detailed information about your application code, we can show you both the client code and the SQL stored procedure template for using table parameters.

Answer 2

You can use below query to select 100 random values ​​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 

Answer 3

I use this for a common set of numbered lines.

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