SQL - Loop & Insert

Experts - I have a stored proc like below:

-- Category Value share

Select AreaName, ProductName, (Select Distinct Time_Period from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and 
TimeSort = 1) as TimePeriod, 'CATEGORY VALUE SHARE' as MeasureName,  [CATEGORY VALUE SHARE] as MeasureValue
from (Select * from MM_Processing_PK_DET_4788) a where TimePeriod in 
(Select Time_Measure from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and TimeSort = 1)

Union All

-- Category Volume Share

Select AreaName, ProductName, (Select Distinct Time_Period from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and 
TimeSort = 1) as TimePeriod, 'CATEGORY VOLUME SHARE' as MeasureName,  [CATEGORY VOLUME SHARE] as MeasureValue
from (Select * from MM_Processing_PK_DET_4788) a where TimePeriod in 
(Select Time_Measure from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and TimeSort = 1)

Union All

-- NUMERICAL DISTRIBUTION

Select AreaName, ProductName, (Select Distinct Time_Period from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and 
TimeSort = 1) as TimePeriod, 'NUMERICAL DISTRIBUTION' as MeasureName,  [NUMERICAL DISTRIBUTION] as MeasureValue
from (Select * from MM_Processing_PK_DET_4788) a where TimePeriod in 
(Select Time_Measure from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and TimeSort = 1)

Union All

-- WEIGHTED DISTRIBUTION

Select AreaName, ProductName, (Select Distinct Time_Period from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and 
TimeSort = 1) as TimePeriod, 'WEIGHTED DISTRIBUTION' as MeasureName,  [WEIGHTED DISTRIBUTION] as MeasureValue
from (Select * from MM_Processing_PK_DET_4788) a where TimePeriod in 
(Select Time_Measure from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and TimeSort = 1)

Union All

-- PRICE PER SU (USD)

Select AreaName, ProductName, (Select Distinct Time_Period from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and 
TimeSort = 1) as TimePeriod, 'PRICE PER SU (USD)' as MeasureName,  [PRICE PER SU (USD)] as MeasureValue
from (Select * from MM_Processing_PK_DET_4788) a where TimePeriod in 
(Select Time_Measure from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and TimeSort = 1)

Union All

-- PRICE PER UNIT (LC)

Select AreaName, ProductName, (Select Distinct Time_Period from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and 
TimeSort = 1) as TimePeriod, 'PRICE PER UNIT (LC)' as MeasureName,  [PRICE PER UNIT (LC)] as MeasureValue
from (Select * from MM_Processing_PK_DET_4788) a where TimePeriod in 
(Select Time_Measure from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and TimeSort = 1)

Union All

-- VOLUME SHARE IN HANDLERS

Select AreaName, ProductName, (Select Distinct Time_Period from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and 
TimeSort = 1) as TimePeriod, 'VOLUME SHARE IN HANDLERS' as MeasureName,  [VOLUME SHARE IN HANDLERS] as MeasureValue
from (Select * from MM_Processing_PK_DET_4788) a where TimePeriod in 
(Select Time_Measure from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and TimeSort = 1)

Open in new window


Like this, I have 1000s of SP's where the only change in each SP is Timesort number. For example, Timesort number can be from 1 to 50 / "n" number.

Requesting help in creating a loop & insert into a table.

Ex:

Every timesort result should be saved in a table. Please help
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
whey not have then 1 stored procedure with the timesort value as parameter?

create procedure do_my_stuff
( @p_timesort int )
as

Select AreaName, ProductName, (Select Distinct Time_Period from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and 
TimeSort = @p_timesort) as TimePeriod, 'CATEGORY VALUE SHARE' as MeasureName,  [CATEGORY VALUE SHARE] as MeasureValue
from (Select * from MM_Processing_PK_DET_4788) a where TimePeriod in 
(Select Time_Measure from MM_Final_TimePeriod_4788 where ExtractName = 'PK_DET' and TimeSort = @p_timesort)

Union All ... etc ...

Open in new window

and you call that procedure like
exec do_my_stuff 1
exec do_my_stuff 2
etc

or in a loop if needed
declare @loop int
set @loop = 1
while @loop <= 10
begin
  exec do_my_stuff @loop
  set @loop = @loop +1
end

Open in new window


not sure what exactly you understand by this:
Every timesort result should be saved in a table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ManjuIT - Project ManagerAuthor Commented:
Guy - Thank you. What I meant was,

the output each SP needs to be saved in a table (same table).

So, what I was looking is, using a variable to Loop.

But I think I'll be able to use your query. Let me check & come back to you shortly.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the procedure could do a "insert into result_table" also if that is the question
and maybe a delete first, so you could rerun the procedure for the same timesort value again and again...
0
ManjuIT - Project ManagerAuthor Commented:
Excellent answer. Precisely what I was expecting. Thanks much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.