Use of spt_values to generate a short series of integer consecutive values

WARNING

In SQL server instance spt_values is an undocumented table in the master database. Do not use this table if you are worried that Microsoft might decide to drop it in a future release of SQL server. As of now it exists in SQL 2008 R2 and it still exists in SQL 2012.

Problem

Generate a series of ID badge numbers in the range between 1001 and 30000. The amount of ID badges that are ordered in one batch is between 20 and 100. Each time an order is created, the ID card records must be inserted automatically for each number in the range. E.g. when a new order is made for badges from 1501 to 1520, then 20 badge records must be created with these numbers. Instead of using a FOR loop in the SQL code or instead of generating my own table of consecutive numbers (which the customer did not prefer) I opted to use the numbers in the spt_values table.

Solution

Given the starting number and ending number, a T-sql statement must generate the range of numbers from start to end.

DECLARE @OrderSerialNrFrom int;

DECLARE @OrderSerialNrTo int;

SET @OrderSerialNrFrom = 1501;

SET @OrderSerialNrTo = 1520;

SELECT DISTINCT n = number + (@OrderSerialNrFrom - 1) 

        FROM master..[spt_values] WHERE number BETWEEN 1 AND (@OrderSerialNrTo - @OrderSerialNrFrom + 1)

GO

The result looks like this:

n
-----------
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520

(20 row(s) affected)

To insert new ID badge records with this range and data from the order, the T-SQL statement looks like this:

DECLARE @OrderSerialNrFrom smallint;

DECLARE @OrderSerialNrTo smallint;

DECLARE @OrderId int;

DECLARE @Comment nvarchar(100);

SET @OrderSerialNrFrom = 1501;

SET @OrderSerialNrTo = 1520;

SET @OrderId = 101;

SET @Comment = N'Expo 2014';

WITH a (SerialNr)

    AS (SELECT DISTINCT n = number + (@OrderSerialNrFrom - 1) 

        FROM master..[spt_values] WHERE number BETWEEN 1 AND (@OrderSerialNrTo - @OrderSerialNrFrom + 1)

        )

    INSERT INTO dbo.Badge (BadgeNr, IsActive, IsBlocked, Orderid, Comment)

    SELECT a.SerialNr, 0, 0, @OrderId, @Comment

        FROM a;

GO

Conclusion

With the aid of spt_vlaues I can easily create small ranges of records without adding complicated code or extra tables to the database.

In SQL 2012, there is a SEQUENCE object that could be used instead of spt_values for generating the number series.