How can I populate a table with all combinations of letters and digits from 01 to ZZ?

Question

I have a tableA (ID INT Identity(1,1) , Name VARCHAR(2))

I want to populate the table with name values from 01 to ZZ: 01 , 02 , 03 , 04,. A1, A2, A3,.. Z1,Z2..

Every value will be distinct.

Answer 1

I think this is what you're looking for (you can always filter out any combinations you don't want by using the WHERE clause):

with characters as
(
select c = char(number) from master..spt_values
where type = 'P' and number between 48 and 57 or number between 65 and 90
)
insert into dbo.tableA ([name])
select
    c1.c + c2.c
from
    characters c1
    cross join characters c2
where
    c1.c + c2.c <> '00'

Please note that using spt_values in production code is not recommended; a better solution is to use your own numbers table. And if name should always be "distinct", you may want to add a unique constraint to the column, if you haven't already.

Answer 2

DECLARE @chars varchar(36)
set @chars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

DECLARE @i1 int = 1
DECLARE @i2 int = 2

WHILE @i1 < LEN(@chars)
BEGIN
    WHILE @i2 < LEN(@chars)
    BEGIN
        INSERT tableA(Name) VALUES (SUBSTRING(@chars, @i1, 1) + SUBSTRING(@chars, @i2, 1))
        SET @i2 += 1
    END
    SET @i1 += 1
    SET @i2 = 1
END