|
|
Posted by Zvonko on 05/29/06 13:46
Thanks for the very prompt answer. That was just was I was looking for. I
redesigned it so the numbers table holds only numbers from 1 to 1000, and
everything works fine. See guys everything is possible with sql if you know
the gurus like Erland.
Many, many thanks to you.
Bye
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97D27DD5A3580Yazorman@127.0.0.1...
> CREATE TABLE example
> (text VARCHAR(30),
> number INTEGER);
>
> insert example(text, number)
> select 'someText', 4
> union
> select 'someText1', 6
> union
> select 'someText2', 8
> go
> SELECT e.text
> FROM example e
> JOIN numbers n ON n.n BETWEEN 1 AND e.number
> Order by e.text
> go
> drop table example
>
> numbers is a table that holds numbers frmo 1 and up. Here is a way to
> set up:
>
>
>
> -- Make sure it's empty.
> TRUNCATE TABLE numbers
> go
> -- Get a temptable with numbers. This is a cheap, but not 100% reliable.
> -- Whence the query hint and all the checks.
> CREATE TABLE #numbers(n int IDENTITY PRIMARY KEY,
> dummy int NULL)
>
> INSERT #numbers (dummy)
> SELECT TOP 1000000 o1.id
> FROM sysobjects o1
> CROSS JOIN sysobjects o2
> CROSS JOIN sysobjects o3
> CROSS JOIN sysobjects o4
> OPTION (MAXDOP 1)
> go
> -- Verify that table does not have gaps.
> IF (SELECT COUNT(*) FROM #numbers) = 1000000 AND
> (SELECT MIN(n) FROM #numbers) = 1 AND
> (SELECT MAX(n) FROM #numbers) = 1000000
> BEGIN
> DECLARE @msg varchar(255)
>
> -- Insert into the real table
> INSERT numbers (n)
> SELECT n FROM #numbers
>
> SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) +
> ' rows into numbers'
> PRINT @msg
> END
> ELSE
> RAISERROR('#numbers is not contiguos from 1 to one million!', 16, -1)
> go
> DROP TABLE #numbers
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|