"Ross Presser" <rp******@imtek.com> wrote in message
news:ja****************************@40tude.net...
On 7 Jul 2004 09:30:03 -0700, ratu wrote:
I'd like to use a stored procedure to insert large amounts of records
into a table. My field A should be filled with a given range of
numbers. I do the following ... but I'm sure there is a better
(faster) way:
select @start = max(A) from tbl where B = 'test1' and C = 'test2'
while @start <= 500000
begin
insert into tbl (A, B, C)
values (@start, 'test1', test2')
set @start = @start +1
end
Here's the DIGITS view-based version:
CREATE VIEW DIGITS (D) AS
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;
If there's no need to eliminate duplicates in the union operation, as here,
use UNION ALL instead. This particular case is trivial but in other
cases the performance improvement can be noticeable.
INSERT INTO tbl (A,B,C)
SELECT D3.D * 1000 + D2.D * 100 + D1.D as [ValA],
'test1' AS [ValB], 'test2' AS [ValC])
FROM DIGITS AS [D1], DIGITS AS [D2], DIGITS AS [D3]
WHERE D3.D * 1000 + D2.D * 100 + D1.D between @LowVal AND @HiVal
You're missing the 10s place here.
Of course, one can fill a table with all integers possibly needed and query
for ranges. Alternatively, one can define a view to calculate the range by
applying constraints to each place value in turn, that is, the ones, tens, hundreds,
etc., as opposed to applying a constraint to each final candidate integer. The
former being more of a branch and bound approach while the latter is
generate and test.
CREATE VIEW Digits (d)
AS
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
-- Return a range [@lower, @upper]
CREATE FUNCTION NonnegativeIntegerRange
(@lower INT, @upper INT)
RETURNS TABLE
AS
RETURN(
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d + 1000 * Thousands.d +
10000 * TenThousands.d AS n
FROM Digits AS TenThousands
INNER JOIN
Digits AS Thousands
ON TenThousands.d BETWEEN @lower/10000 AND @upper/10000 AND
(TenThousands.d <> @upper/10000 OR
Thousands.d <= (@upper%10000)/1000) AND
(TenThousands.d <> @lower/10000 OR
Thousands.d >= (@lower%10000)/1000)
INNER JOIN
Digits AS Hundreds
ON (TenThousands.d <> @upper/10000 OR
Thousands.d <> (@upper%10000)/1000 OR
Hundreds.d <= (@upper%1000)/100) AND
(TenThousands.d <> @lower/10000 OR
Thousands.d <> (@lower%10000)/1000 OR
Hundreds.d >= (@lower%1000)/100)
INNER JOIN
Digits AS Tens
ON (TenThousands.d <> @upper/10000 OR
Thousands.d <> (@upper%10000)/1000 OR
Hundreds.d <> (@upper%1000)/100 OR
Tens.d <= (@upper%100)/10) AND
(TenThousands.d <> @lower/10000 OR
Thousands.d <> (@lower%10000)/1000 OR
Hundreds.d <> (@lower%1000)/100 OR
Tens.d >= (@lower%100)/10)
INNER JOIN
Digits AS Ones
ON (TenThousands.d <> @upper/10000 OR
Thousands.d <> (@upper%10000)/1000 OR
Hundreds.d <> (@upper%1000)/100 OR
Tens.d <> (@upper%100)/10 OR
Ones.d <= @upper%10) AND
(TenThousands.d <> @lower/10000 OR
Thousands.d <> (@lower%10000)/1000 OR
Hundreds.d <> (@lower%1000)/100 OR
Tens.d <> (@lower%100)/10 OR
Ones.d >= @lower%10)
)
SELECT n
FROM NonnegativeIntegerRange(20000, 20009)
ORDER BY n
n
20000
20001
20002
20003
20004
20005
20006
20007
20008
20009
--
JAG