CODEWITHSUNDEEP
sqlsql-serversql-server-2008temp-tables
Bala
Bala

Reputation: 4547

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

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.

Upvotes: 6

Views: 24832

Answers (4)

Rahul Yadav
Rahul Yadav

Reputation: 41

    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
)

Upvotes: 1

HoggZilla
HoggZilla

Reputation: 11

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

Upvotes: 1

mehdi lotfi
mehdi lotfi

Reputation: 11581

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

Upvotes: 2

MatBailie
MatBailie

Reputation: 86765

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.

Upvotes: 7

Related Questions