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
 using while loop in sql stored procedure

Author  Topic 

DocHoliay66
Starting Member

2 Posts

Posted - 2014-07-31 : 10:12:17
I have two tables one hold all Friday dates for given year (col1 ID, col2 date), second table have three column col1, col22, col3. What I need to do is insert the first Friday date into the second table for col1 and second Friday into col2, third Friday in col3, fourth Friday in col1 and the process repeat until all Friday dates are inserted. I would like to use While loop but stuck on how to iterate through table one to get the data.

Any help would be greatly appreciated.

Example:
table1 data
1 01/03/2014
2 01/10/2014
3 01/17/2014
4 01/24/2014
5 01/31/2014
6 02/07/2014
7 02/14/2014
8 02/21/2014
table2 data should look after insert compelete.
col1 col2 col3
01/03/2014 01/10/2014 01/17/2014
01/24/2014 01/31/2014 02/07/2014
02/14/2014 02/21/2014

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-31 : 10:22:19
quote:
Originally posted by DocHoliay66

I have two tables one hold all Friday dates for given year (col1 ID, col2 date), second table have three column col1, col22, col3. What I need to do is insert the first Friday date into the second table for col1 and second Friday into col2, third Friday in col3, fourth Friday in col1 and the process repeat until all Friday dates are inserted. I would like to use While loop but stuck on how to iterate through table one to get the data.

Any help would be greatly appreciated.

Example:
table1 data
1 01/03/2014
2 01/10/2014
3 01/17/2014
4 01/24/2014
5 01/31/2014
6 02/07/2014
7 02/14/2014
8 02/21/2014
table2 data should look after insert compelete.
col1 col2 col3
01/03/2014 01/10/2014 01/17/2014
01/24/2014 01/31/2014 02/07/2014
02/14/2014 02/21/2014

If your requirement is that you have to use a while loop you certainly can, but it is easier and more efficient to write the query using set based operators. Here is an example of how you could write a set based query to accomplish what you are looking for:
SELECT * FROM
(
SELECT [DateCol], (Idcol-1)/3 AS ROW, (Idcol-1)%3 AS COL
FROM Table1
)s PIVOT
( MAX([Date]) FOR COL IN ([0],[1],[2])) p
Go to Top of Page

DocHoliay66
Starting Member

2 Posts

Posted - 2014-07-31 : 11:42:41
Hi James K,

No, I don't have to use "While Loop", I tried your solution and it work just the way I wanted.

Thank you very much for you help.

DocHoliday
Go to Top of Page
   

- Advertisement -