Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting Multiple Records Using Range Numbers Expand / Collapse
Author
Message
Posted Wednesday, June 24, 2009 5:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 6:27 AM
Points: 68, Visits: 55
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 Number Other 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
Post #740910
Posted Wednesday, June 24, 2009 5:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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




The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #740916
Posted Wednesday, June 24, 2009 8:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 9,928, Visits: 11,206
Dynamic tally:

;WITH	Numbers (N) AS
(
SELECT TOP (25000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master.sys.columns
CROSS
JOIN master.sys.columns C2
)
SELECT 'A' + RIGHT((10000000000 + N), 9)
FROM Numbers;

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #741047
Posted Wednesday, June 24, 2009 8:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #741085
Posted Wednesday, June 24, 2009 9:08 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 9,928, Visits: 11,206
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #741116
Posted Wednesday, June 24, 2009 9:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
I knew that any other constant expression should work equal. Just been unsure about the NULL.

Thanks!
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #741142
Posted Wednesday, June 24, 2009 3:16 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 9,928, Visits: 11,206
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #741405
Posted Wednesday, June 24, 2009 9:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 6:27 AM
Points: 68, Visits: 55
Thnx folks for the replies!
Post #741529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse