Forum Discussion
Execute stored procedure and save values in a table
Hello, I have around 1000 values that I'd like to execute against my SQL code and store the resulting data in a table for further analysis.
Here is an example of my SQL code:
INSERT INTO Summary_Final ( PD_Deadline, stock, sell, [count] )
SELECT x.PD_Deadline, x.stock, x.sell, x.[count]
FROM (
SELECT
-0.15 as PD_Deadline,
a.stock, a.sell, Count(*) AS [count]
FROM (
SELECT Data.Stock, Data.Date, Data.Buy_price*(1+(0.05)) AS Sell
FROM Data ) AS a
WHERE a.[Flag]="Y"
GROUP BY a.stock, a.sell
) as x;
The values of -0.15 and 0.05 in the code serve as adjustable parameters. If I were to execute the code manually, I'd modify these parameters individually for approximately 1000 instances and run the code for each specific set of values.
It would be more efficient to store these parameters in a table, transform the existing code into a function, and then invoke the function within a loop.
As a new bee, I would appreciate detailed guidance on this matter. Thank you in advance.
Cross-posted, with additional responses, at https://www.access-programmers.co.uk/forums/threads/execute-stored-procedure-and-save-values-in-a-table.330036/
You didn't indicate where those variable values come from nor how they are determined, so it's a bit ambiguous still.
That said, yes, a table would be a good strategy assuming you can populate it with the appropriate values for each of the other values involved from the "Data" table (That's a poor choice for a table name by the way; it's too generic. Perhaps you could figure out a meaningful name for it by identifying what data is involved.)
Beyond that, it's hard to offer specific suggestions because we have no way to determine how or why those variable values are determined and how they relate to the "Data" values.
But you could clarify things and help us provide more focused suggestions.
Thank you for your help thus far...
I am unable to reproduce the suggestion in my database, so I'm sharing it with you for your examination and input.
Here is a quick summary of my tables:
Data: This table encompasses the main data.
TBL_Combinations: This table comprises the variables that I manually include in my append query.
Summary_Final: I store the data in this table after executing it against the variables stored in TBL_Combinations.
These are the manual steps I follow to achieve the desired output, and I'm seeking automation for this process:
1. Initially, I open the TBL_Combinations and document the variables. For instance, the first line contains PD: -0.15; Buy_1: -0.1; Buy_2: -0.1%; Sell: 0.05.
2. Subsequently, I access the "Query - Append data" and switch to the SQL view.
3. Following that, I modify the variables as indicated in the provided snapshot.
4. After making the adjustments, I execute the query to store the summary in the Summary_Final table.
5. I repeat steps 1 to 4 for the subsequent combinations.
Desired outcome
I want the manual process to be automated.
Thanks again for your assistance
