"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