CODEWITHSUNDEEP
sqlsql-servert-sql
user7756155
user7756155

Reputation:

Inserting data into SQL Server table based on a cross

I want to insert data in two columns(Alphabet, Number) in following format:

A,101
A,102
A,103
A,201
A,202
A,203
...
A,1203
...
B,101
B,102
B,103
....
Z,1203

Attempted solution:

I am able to achieve this by making two tables with one column each. In first table I have kept A-Z, so 26 rows.

In second table I have kept numbers 101, 102, 103, 201, 202, 203...1203.

And then I used following query

Insert into testtable
    select a.letter, n.ID  
    from Alphabet a 
    cross join Number n

select * from Alphabet

I achieved the required result, but I am not happy with this tedious approach. Also, I attempted while loop, but failed so far.

Can you please help in finding and suggesting a better approach to achieve this requirement?

Upvotes: 0

Views: 63

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

If you don't have a Numbers/Tally Table, you can use a an ad-hoc tally table.

Example

Declare @N1 int = 101
Declare @N2 int = 1203

Select C,N
 From (Select Top (@N2-@N1+1) N=@N1-1+Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2 ) A
 Cross Join (Select Top (26) C=char(65-1+Row_Number() Over (Order By (Select NULL))) From master..spt_values n1 ) B
 Order by C,N

Returns 28,678 rows

C   N
A   101
A   102
A   103
...
Z   1201
Z   1202
Z   1203

Upvotes: 3

Related Questions