How to speed up SQL script which creates a large table of AlphaNumeric codes

Viewed 55

I have the following script, that generates 175 Million(!) unique Alphanumeric codes in the format 'ABC-1234', ranging from AAA-0000 to ZZZ-9999.

Running this script as-is, on my dedicated MS SQL 2016 box takes 20 hours. What's the best way to speed this up? As you can tell from the script, my SQL skills are somewhat lacking !

There's an Identity (int) Column, ID, and the column for the CODE (nvarchar(20)) itself. The two columns make up the primary key:

CREATE TABLE [dbo].[ORDERED_CODES](
[ID] [int] NOT NULL,
[Code] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_ORDERED_CODES] PRIMARY KEY CLUSTERED 
(
[ID] ASC,
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,                 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The script:

DECLARE @Alpha1 INT;
DECLARE @Alpha2 INT;
DECLARE @Alpha3 INT;
DECLARE @Num INT;

-- alpha elements 'ABC'
SET @Alpha1 = 65;
SET @Alpha2 = 65;
SET @Alpha3 = 65;

-- number element '9999'
SET @Num = 0;

-- temporary holders
DECLARE @FINALCODE Nvarchar(50);
DECLARE @CODE1 Nvarchar(50);
DECLARE @CODE2 Nvarchar(50);
DECLARE @CODE3 Nvarchar(50);

WHILE @Alpha1 < 91
BEGIN
   SET @CODE1 = CHAR(@Alpha1)
       WHILE @Alpha2 < 91
        BEGIN
       SET @CODE2 = @CODE1 + CHAR(@Alpha2)
       SET @Alpha2 = @Alpha2 +1
            WHILE @Alpha3 < 91
                BEGIN
                   SET @CODE3 = @CODE2 + CHAR(@Alpha3)
                   SET @Alpha3 = @Alpha3 +1
                        WHILE @Num < 10000
                            BEGIN
                               SET @FINALCODE = RIGHT('0000'+ CAST(@Num as nvarchar(4)),4) + CHAR(45) + @CODE3        
                               SET @Num = @Num +1
                               INSERT INTO ORDERED_CODES (CODE) VALUES (@FINALCODE)
                            END
                        SET @FINALCODE = null
                        SET @Num = 0
                END
            SET @Alpha3 = 65
    END
    SET @Alpha2 = 65

    SET @Alpha1 = @Alpha1 +1   
END;

Any and all thoughts are gratefully appreciated!

3 Answers
Related