Help with writing a loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with writing a loop

Author

Topic

chrisbucci8
Starting Member

2 Posts

Posted - 2010-05-14 : 10:31:35

Here is what I am trying to do:

Write a script that will loop through a column in a given table and insert a series of values until the entire column is filled with the appropriate values. Here is the real time example that I will use to explain better.

I did this for testing to see if it in fact did what I needed it to do. When I saw the one line altered in the database I then went into the app to verify the change I wanted was made which it did.

insert into TABLE NAME (COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7)
values ('1', '2', '3', '4', '5', '6', '7')

Then
select distinct COLUMN2 from TABLE NAME
which gave me a count of 2580.

Last the values in COLUMN3 would only range between the same 30 numbers. This is how the loop would know when to end. Once it added that 30th value it would then go to the next value in COLUMN2.

Make sense? Any help would be great. I'm sure I need to use a insert into with a select statement or could create a function but any help would be greatly appreciated. Thanks.

Chris

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-14 : 10:37:23

not really

Post some more sample data, ddl, and what the final results are suppose to look like

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

chrisbucci8
Starting Member

2 Posts

Posted - 2010-05-14 : 11:20:10

Here is what I entered:

select * from DiagSpecialty where DiagID = 20343

The results where:
DiagSpecialtyID DiagID SpecialtyMID Created CreatedBy LastModified LastModified By
1 92 20343 188 2010-05-14 cbucci 2010-05-14 cbucci
2. 93 20343 1716 2010-05-14 cbucci 2010-05-14 cbucci
3. 94 20343 1717 2010-05-14 cbucci 2010-05-14 cbucci
4. 95 20343 1718 2010-05-14 cbucci 2010-05-14 cbucci
5. 96 20343 1719 2010-05-14 cbucci 2010-05-14 cbucci
6. 97 20343 1720 2010-05-14 cbucci 2010-05-14 cbucci
7. 98 20343 1721 2010-05-14 cbucci 2010-05-14 cbucci
8. 99 20343 1722 2010-05-14 cbucci 2010-05-14 cbucci
9. 100 20343 1723 2010-05-14 cbucci 2010-05-14 cbucci
10 101 20343 1724 2010-05-14 cbucci 2010-05-14 cbucci
11. 102 20343 1814 2010-05-14 cbucci 2010-05-14 cbucci
12. 103 20343 2935 2010-05-14 cbucci 2010-05-14 cbucci
13. 104 20343 12281 2010-05-14 cbucci 2010-05-14 cbucci
14. 105 20343 12282 2010-05-14 cbucci 2010-05-14 cbucci
15. 106 20343 12283 2010-05-14 cbucci 2010-05-14 cbucci
16. 107 20343 12284 2010-05-14 cbucci 2010-05-14 cbucci
17. 108 20343 12285 2010-05-14 cbucci 2010-05-14 cbucci
18. 109 20343 12286 2010-05-14 cbucci 2010-05-14 cbucci
19. 110 20343 12287 2010-05-14 cbucci 2010-05-14 cbucci
20. 111 20343 12288 2010-05-14 cbucci 2010-05-14 cbucci
21. 112 20343 12289 2010-05-14 cbucci 2010-05-14 cbucci
22. 113 20343 12290 2010-05-14 cbucci 2010-05-14 cbucci
23. 114 20343 12291 2010-05-14 cbucci 2010-05-14 cbucci
24. 115 20343 12292 2010-05-14 cbucci 2010-05-14 cbucci
25. 116 20343 12293 2010-05-14 cbucci 2010-05-14 cbucci
26. 117 20343 12294 2010-05-14 cbucci 2010-05-14 cbucci
27. 118 20343 12295 2010-05-14 cbucci 2010-05-14 cbucci
28. 119 20343 12296 2010-05-14 cbucci 2010-05-14 cbucci
29. 120 20343 12297 2010-05-14 cbucci 2010-05-14 cbucci
30. 121 20343 12298 2010-05-14 cbucci 2010-05-14 cbucci

Then I ran this for another DiagID

select * from DiagSpecialty where DiagID = 20344
The results were
DiagSpecialtyID DiagID SpecialtyMID Created CreatedBy LastModified LastModified By
1 122 20344 188 2010-05-14 cbucci 2010-05-14 cbucci
2. 123 20344 1716 2010-05-14 cbucci 2010-05-14 cbucci
3. 124 20344 1717 2010-05-14 cbucci 2010-05-14 cbucci
4. 125 20344 1718 2010-05-14 cbucci 2010-05-14 cbucci
5. 126 20344 1719 2010-05-14 cbucci 2010-05-14 cbucci
6. 127 20344 1720 2010-05-14 cbucci 2010-05-14 cbucci
7. 128 20344 1721 2010-05-14 cbucci 2010-05-14 cbucci
8. 129 20344 1722 2010-05-14 cbucci 2010-05-14 cbucci
9. 130 20344 1723 2010-05-14 cbucci 2010-05-14 cbucci
10. 131 20344 1724 2010-05-14 cbucci 2010-05-14 cbucci
11. 132 20344 1814 2010-05-14 cbucci 2010-05-14 cbucci
12. 133 20344 2935 2010-05-14 cbucci 2010-05-14 cbucci

So what I need to do is basically insert values for lines 13-30 for this DiagID

Next string I ran was:

select distinct DiagID from DiagSpecialty
which returned
DiagID
1. 20343
2. 20344
3. 20345
4. 20346
5. 20347
...
2578. 22920
2579. 22921
2580. 22922

So for each DiagID I need to add lines 13-30

That a little more clear?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-14 : 11:41:35

Is DiagSpecialtyID an autonum / identity column?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-14 : 11:54:48

Just an idea, but you have to test:

while exists(select top 1 DiagID from DiagSpecialty group by DiagID having count(*)=12)
begin

insert DiagSpecialty(DiagID, SpecialtyMID, [Created], [CreatedBy], LastModified, [LastModified By])
select
(select top 1 DiagID from DiagSpecialty group by DiagID having count(*)<30 order by DiagID),
SpecialtyMID, [Created], [CreatedBy], LastModified, [LastModified By]
from DiagSpecialty
where DiagID = 20343 and DiagSpecialtyID > 103

end

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources