Generate sequence of numbers
Luca
I need a procedure for generate a sequence of numbers, over
2,000,000!!! and fill a table (only once), the format is simple:
0000001
0000002
0000003
0000004
...
1900442
...
2000000
and so on...
with TSQL is possible or VB is better?
Thx a lot!
oj
into #tmp1
from syscolumns s1, syscolumns s2, syscolumns s3
go
select right(10000000+i,7) as j
into #tmp2
from #tmp1
go
select * from #tmp2
go
--
-oj
RAC v2.1 & QALite!
http://www.rac4sql.net
"Luca" <luca_...@hotmail.com> wrote in message
news:5027603c.03042...@posting.google.com...
John Gilson
CREATE TABLE Digits
(
d TINYINT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)
INSERT INTO Digits
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
-- Generates integers from 0 (inclusive) to 10,000,000 (exclusive)
CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d +
10 * Tens.d +
100 * Hundreds.d +
1000 * Thousands.d +
10000 * TenThousands.d +
100000 * HundredThousands.d +
1000000 * Millions.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands
CROSS JOIN
Digits AS HundredThousands
CROSS JOIN
Digits AS Millions
CREATE TABLE NaturalNumbers
(
n INT NOT NULL PRIMARY KEY CHECK (n > 0)
)
CREATE PROCEDURE InsertNaturalNumbers
@n INT -- inclusive upper bound
AS
INSERT INTO NaturalNumbers (n)
SELECT n
FROM NonnegativeIntegers
WHERE n > (SELECT COALESCE(MAX(n), 0) FROM NaturalNumbers) AND
n <= @n
-- insert natural numbers to 2,000,000 (inclusive)
EXEC InsertNaturalNumbers 2000000
Regards,
jag
oj
of course, we could easily generate a derived table using "SELECT Ones.d +
10 * Tens.d +
100 * Hundreds.d +..." on the fly and/or using 'select/into'
which is minimal logged.
--
-oj
"John Gilson" <j...@acm.org> wrote in message
news:qozra.27575$su3.3...@twister.nyc.rr.com...
> "Luca" <luca_...@hotmail.com> wrote in message
> news:5027603c.03042...@posting.google.com...
Joe Celko
You say "numbers" but show character strings in the sample data.
CREATE TABLE Digits
(i CHAR(1) NOT NULL PRIMARY KEY);
INSERT INTO Digits VALUES('0');
INSERT INTO Digits VALUES('1');
...
INSERT INTO Digits VALUES('9');
CREATE TABLE Sequence
(seq CHAR(7) NOT NULL PRIMARY KEY);
INSERT INTO Sequence
SELECT D1.i + D2.i + D3.i + D4.i + D5.i + D6.i + D7.i
FROM Digits AS D1,
Digits AS D2,
Digits AS D3,
Digits AS D4,
Digits AS D5,
Digits AS D6,
Digits AS D7;
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Joe Celko
You say "numbers" but show character strings in the sample data. I will
assume that you meant character strings.
CREATE TABLE Digits
(i CHAR(1) NOT NULL PRIMARY KEY);
INSERT INTO Digits VALUES('0');
INSERT INTO Digits VALUES('1');
...
INSERT INTO Digits VALUES('9');
CREATE TABLE Sequence
(seq CHAR(7) NOT NULL PRIMARY KEY);
INSERT INTO Sequence
SELECT D1.i + D2.i + D3.i + D4.i + D5.i + D6.i + D7.i
FROM Digits AS D1,
Digits AS D2,
Digits AS D3,
Digits AS D4,
Digits AS D5,
Digits AS D6,
Digits AS D7
WHERE D1.i IN ('0','1','2')
AND (D1.i + D2.i + D3.i + D4.i + D5.i + D6.i + D7.i)
<= '2000000';
The first predicate is to save the secodn predicate some work.
John Gilson
> insert/select could be quite costly for such large number.
Yes, but I'm assuming this will be done but once and used more
than that. I often find myself longing for just such a table, whether
it be a "real" or "virtual" one. Well, perhaps not longing, but you
get my drift...
Regards,
jag
oj
--
-oj
RAC v2.1 & QALite!
http://www.rac4sql.net
"John Gilson" <j...@acm.org> wrote in message
news:0Wzra.27586$su3.3...@twister.nyc.rr.com...