|
|
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;
|
|