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.
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.
Given the starting number and ending number, a T-sql statement must generate the range of numbers from start to end.
The result looks like this:
(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:
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.