SQL advanced -> Create tally table of 10000 sequential numbers in MS-Access

In this example we will create tally table of 10000 sequential numbers using 4 SQL commands.

Create tally table:

CREATE TABLE num_seq
(n Autoincrement,
temp_no INT,
CONSTRAINT pk_n PRIMARY KEY (n));

Following insert creates one record in num_seq and next to it UNION query that retrieves it 10 times. We'll save this query under name Union10 .

INSERT INTO num_seq ( temp_no )
VALUES (1);

SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq
  UNION ALL
SELECT temp_no AS [no] FROM num_seq;

Following insert creates set of 9999 records, that will cause Autoincrement function to produce 9999 sequential numbers in column n (10000 together with the initial record).

INSERT INTO num_seq ( temp_no )
SELECT TOP 9999 a.no
FROM Union10 AS a, Union10 AS b, Union10 AS c, Union10 AS d;

Alter tally table to remove temp column:

ALTER TABLE num_seq DROP COLUMN temp_no;
ALTER TABLE num_seq ALTER COLUMN n INT;



sqlexamples.info