434,587 Members | 1,020 Online
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

# fastest way to insert range/sequence

 P: n/a 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 another question is, how to prevent that another user inserts the same numbers into the field A? Thanks a lot for any help! ratu Jul 20 '05 #1
5 Replies

 P: n/a "ratu" wrote in message news:e6**************************@posting.google.c om... 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 another question is, how to prevent that another user inserts the same numbers into the field A? Thanks a lot for any help! ratu One possible solution is an auxiliary table of numbers, or a UDF as described in this post: http://groups.google.com/groups?q=it...ftngp13&rnum=1 You could then do something like this: insert into tbl (A, B, C) select n, 'test1', 'test2' from dbo.fn_nums(@start, 500000) As for preventing duplicate entries, you can use a primary key or unique constraint to prevent duplicates, depending on your data model. If necessary, you can also use a check constraint to ensure that the table will only accept a certain range of numbers. Simon Jul 20 '05 #2

 P: n/a 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; 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 another question is, how to prevent that another user inserts the same numbers into the field A? Create a unique index on field A. The other user will receive an error. Jul 20 '05 #3

 P: n/a "Ross Presser" 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 Jul 20 '05 #4

 P: n/a On Fri, 09 Jul 2004 03:18:31 GMT, John Gilson wrote: 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. Thanks. I noticed you using UNION ALL before, but never understood it, because I never looked up the explanation of UNION ALL. You're missing the 10s place here. D'oh! 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. Thanks for your clearly expressed improvement of my half-baked ideas. :) I still have two very active SQL 6.5 servers, so I tend not to think of UDF solutions. Jul 20 '05 #5

 P: n/a "Ross Presser" wrote in message news:1x******************************@40tude.net.. . On Fri, 09 Jul 2004 03:18:31 GMT, John Gilson wrote: 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. Thanks. I noticed you using UNION ALL before, but never understood it, because I never looked up the explanation of UNION ALL. It's worth knowing when you're taking the union of significant result sets. It's like knowing when and when not to use DISTINCT. In this case it obviously isn't an issue other than reinforcing good practice. You're missing the 10s place here. D'oh! It's even easier to do when you go into the millions and beyond. 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. Thanks for your clearly expressed improvement of my half-baked ideas. :) Your generate-and-test approach is an obvious and completely reasonable solution. The branch-and-bound approach, run on SQL Server 2000, does seem, in cursory testing, to be over twice as fast. Though it is more verbose and perhaps less immediately clear. I still have two very active SQL 6.5 servers, so I tend not to think of UDF solutions. The UDF here is just for named packaging. Could've simply provided the SELECT using variables for the range bounds. -- JAG Jul 20 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.