while condition or loop

Question

Post reply

while condition or loop

sdgray01

SSC-Addicted

Points: 401
More actions
August 7, 2011 at 6:58 am

#241429

Hi,
A Simple question i hope..... but how can i get the following syntax to repeat 3 times. Each time looking at the data its just appended and adding another year on top, without using repeating the statement with a go at the end 🙁
Thanks
INSERT INTO [TEMP_Table1](year, Sex, GeographyCode, SubCategory, Age, [Population])
SELECT
MAX(LEFT(year, 4)) + 1 AS year,
Sex, GeographyCode,SubCategory,Age,[Population]
FROM [TEMP_Table1]
GROUP BY Sex, GeographyCode, SubCategory, Age, [Population]

Viewing 9 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

SELECT

MAX(LEFT(year, 4)) + dtMultiplier.RN AS year,

Sex, GeographyCode,SubCategory,Age,[Population]

FROM [TEMP_Table1]

CROSS JOIN (SELECT TOP (@Years) ROW_NUMBER() OVER (ORDER BY object_id) AS RN FROM sys.objects) dtMultiplier

GROUP BY Sex, GeographyCode, SubCategory, Age, [Population]

Here's I'm guessing that you need to generate data... but could you confirm what you are trying to do? Maybe there's a better way than that.

sdgray01 SSC-Addicted Points: 401 More actions · Answer 2

Thank you for the response, but basically I just need it to run that code 3 times, each time it will look at the table, find the lastest year add 1 to it and append to the table before repeating and reapeating again. Basically just a loop if possible

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

What's still unclear is the output you want.

When running my query, what's wrong with the output you get?

Edit, I think you might need to use a derived table AFTER the group by. Can't run my code now so I can't make sre it works 100%. Either that or put RN in the group by which shouldn't mess with the rest of the query.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

sdgray01 (8/7/2011)
Thank you for the response, but basically I just need it to run that code 3 times, each time it will look at the table, find the lastest year add 1 to it and append to the table before repeating and reapeating again. Basically just a loop if possible

Ninja's_RGR'us code DOES loop @Years number of times. It just may be that you've not seen such a "loop" before. Some of us call it a "Pseudo-Cursor" and, in this case, it uses the "looping" that SQL Server uses behind the scenes of a Cross Join to magically "repeat the code". This type of "loop" will absolutely blow the doors off of any "real" Cursor or While Loop you could write performance-wise.

He also didn't include an "INSERT" because that's the easy part. He was just trying to show how to get the SELECT to "loop" without writing an explicit loop.

Please take a minute from your busy day and post some example data in a "readily consumable" format. Please don't assume that you know how to do that... please read the article at the first link in my signature lines below for the best way to help me help you and then I'll explain the whole shootin' match because it's a super important concept in writing high performance code in SQL. I know you might be in a bit of a rush to get something done, but it'll be well worth the time, I promise. 🙂

--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)

sdgray01 SSC-Addicted Points: 401 More actions · Answer 5

Thank you both for your help, i do appreciate that. i have finally managed to crack it and all seems to be working now. In the end i was able to just devise a while statement, by comparing two Select statements, one with the 3 years added to the max year found - if that makes sense.

Thanks Again

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Jeff puts it best (check his signature). Stop trying to do something to a row and think what you want to do to a column.

This allows you to stay away from while loops, cusors and the likes.

While your solution might get the job done it's not going to be the fastest.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

sdgray01 (8/7/2011)
Thank you both for your help, i do appreciate that. i have finally managed to crack it and all seems to be working now. In the end i was able to just devise a while statement, by comparing two Select statements, one with the 3 years added to the max year found - if that makes sense.
Thanks Again

Thanks for the feedback but, now that you've just learned how to create a loop, now is the best time to learn how to avoid them. 😉

If you post some data in the format I requested on my previous post along with the code you've just written, I'll be happy to show you several reasons why you should learn how to avoid loops even for something as simple as this.

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

I think this is an excellent read for you... next time you got 15 minutes to invest in yourself.

http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/