Inserting Multiple Records Using Range Numbers

Question

Inserting Multiple Records Using Range Numbers

kolangara_anand

Mr or Mrs. 500

Points: 552
More actions
June 24, 2009 at 5:00 am

#97672

Hi,
I have an SP in which I receive a range of stock numbers and I need to insert each number in that range as a individual record in a table. Before inserting into the table I have to format the number store it as a varchar of size 10. The attributes other than this stock number would be same for all those records. I would need to insert upto 5000 such records.
For example I receive an input as 1 -100 then the resultant table should be like as below
Stock NumberOther Parameters
A000000001 xyz
A000000002 xyz
A000000003 xyz
………..…...… xyz
A000000100 xyz
Currently in the SP there is loop running between from and to numbers and inserting 1 record at a time. I believe there should be a better way than that. Any pointers on how I can achieve this would be great.
Thnx,
Anand

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Using a a Tally table to create this range of items is just simple. If you don't know what a Tally table is, please search this site. You will find a great article published by Jeff Moden.

DECLARE

@from INT,

@to INT

SELECT

@from = 1,

@to = 100

SELECT

'A' + REPLICATE('0', 9 - LEN(CONVERT(VARCHAR(10), N))) + CONVERT(VARCHAR(10), N)

FROM Tally

WHERE N BETWEEN @from AND @to

Paul White SSC Guru Points: 150467 More actions · Answer 2

Dynamic tally:

;WITHNumbers (N) AS

(

SELECTTOP (25000)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROMmaster.sys.columns

CROSS

JOINmaster.sys.columns C2

)

SELECT'A' + RIGHT((10000000000 + N), 9)

FROMNumbers;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hey Paul

A little side question:

You work with this row-number function:

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

Is there any difference to this:

ROW_NUMBER() OVER (ORDER BY (SELECT 1))

Sure, the results are equal but do you know if they work equal? Both work with constant expressions but my thought was NULL is always something special.

Cheers

Flo

Paul White SSC Guru Points: 150467 More actions · Answer 4

Hey Flo,

Nope - no difference at all, it just depends on my mood 🙂

ROW_NUMBER() OVER (ORDER BY (SELECT -42.7))

...works just as well.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

I knew that any other constant expression should work equal. Just been unsure about the NULL.

Thanks!

Flo

Paul White SSC Guru Points: 150467 More actions · Answer 6

Florian Reischl (6/24/2009)
I knew that any other constant expression should work equal. Just been unsure about the NULL.

Oh I see.

I guess I started using NULL because it somehow emphasises the fact the the 'constant' isn't important or even relevant - it's just required by the syntax, as you know. There's no advantage or disadvantage to using NULL - as far as I know.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kolangara_anand Mr or Mrs. 500 Points: 552 More actions · Answer 7

kolangara_anand

Mr or Mrs. 500

Points: 552

June 24, 2009 at 9:53 pm

#1015067

Thnx folks for the replies!