Generating records without using cursor

Question

Post reply

Generating records without using cursor

Priya004

Ten Centuries

Points: 1368
More actions
June 7, 2009 at 1:13 am

#210333

I have a Column with some values as shown below
Table A
C1
3
4
5
6
I have to generate total 18records with some autogenerated ID column in table B
As the frist value of C is 3 i have to generate 3 records like wise for all the values i have to generate that many number of records in table B
Plz suggest a solution for the ablve scenarioo with out using cursors
Thanks
Priya

Viewing 13 posts - 1 through 12 (of 12 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi Priya,

would you please show us what you've tried so far so we can help you to make your code performing better.

I think it's a better idea than just throw your request in and wait for the coding result from us...

What you're looking for seems like a job for the Tally table. You might want to search for it in this forum or start with http://www.sqlservercentral.com/articles/T-SQL/62867/.

Please read the mentioned article and try to apply it to your situation. If you're struggling with it, get back here and we'll help you to understand it and make it work.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Priya004 Ten Centuries Points: 1368 More actions · Answer 2

I have gone through the example

But my concern is how can we loop through all the values of a column with out having cursor in the above example?

Thanks,

Priya.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Indu (6/7/2009)
I have gone through the example
But my concern is how can we loop through all the values of a column with out having cursor in the above example?
Thanks,
Priya.

As Lutz indicated, a tally table is probably what you need. My problem is I'm not sure what you are expecting as output based on the data you have provided.

If you could show us the expected results based on the data in your original post that would be more helpful. Please realize, some of us need a visual clue as to what you are attempting. Kust giving a description isn't always enough information.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Since you "investigated" what a tally table is, the following code should be self explaining. It will return 18 lines as requested.

-- build sample table and insert data

DECLARE @a TABLE (C1 INT)

INSERT INTO @a

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6

-- select the requested 18 rows (3*'3' + 4*'4' + 5*'5' + 6+'6')

SELECT a.C1 FROM @a a

INNER JOIN tally ON a.c1 >= tally.n

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Indu (6/7/2009)
I have gone through the example

Heh... is it just me or does that really sound pompous and arrogant? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff Moden (6/7/2009)
Indu (6/7/2009)
I have gone through the example
Heh... is it just me or does that really sound pompous and arrogant? 😉

Nope, it isn't just you. 😉

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Jeff Moden (6/7/2009)
Indu (6/7/2009)
I have gone through the example
Heh... is it just me or does that really sound pompous and arrogant? 😉

No, i wouldn't say that it was pompous or arrogant (I've used this phrase before, so I hope it isn't). Of course, I wouldn't say that it sounds credible either.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

LutzM SSC Guru Points: 107049 More actions · Answer 8

I was wondering about his reply too, since the two statements (regarding the article and the correlation to the original subject) within one post don't seem to match up very well...

I'm also a little confused what kind of business case would require a coding like it's been asked for. I'm under the impression that the "business case" is a little more theoretical than real life.

But other than the OP no one knows so far, or do you have had any need in the past to solve an issue like this? Just being curious...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

lmu92 (6/7/2009)
I'm also a little confused what kind of business case would require a coding like it's been asked for. I'm under the impression that the "business case" is a little more theoretical than real life.

Well, homework is one possibility.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

RBarryYoung (6/7/2009)
Jeff Moden (6/7/2009)
Indu (6/7/2009)
I have gone through the example
Heh... is it just me or does that really sound pompous and arrogant? 😉
No, i wouldn't say that it was pompous or arrogant (I've used this phrase before, so I hope it isn't). Of course, I wouldn't say that it sounds credible either.

Put the words back into the context they were offered. OP is basically saying "I already told ya sucker... now fix my problem."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

lmu92 (6/7/2009)
I was wondering about his reply too, since the two statements (regarding the article and the correlation to the original subject) within one post don't seem to match up very well...
I'm also a little confused what kind of business case would require a coding like it's been asked for. I'm under the impression that the "business case" is a little more theoretical than real life.
But other than the OP no one knows so far, or do you have had any need in the past to solve an issue like this? Just being curious...

I seen this type of problem many times. Normally, it comes with a start and end date and someone wants to generate a row for each date in the range.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 12

Jeff Moden (6/7/2009)
I seen this type of problem many times. Normally, it comes with a start and end date and someone wants to generate a row for each date in the range.

Well, in that case we'd talk about two columns (start date and end date) which would be almost a "standard case".

But here the request is based on a given number in a single column. This almost looks like a trial of reverse engineering of a grouped-by result table. I think Barry's guess is a lot closer... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]