asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Manju

ASKER

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.

Guy Hengel [angelIII / a3]

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

Manju

ASKER

Excellent answer. Precisely what I was expecting. Thanks much.

SQL - Loop &amp; Insert

SQL - Loop & Insert