MySQL How to Insert into [Temp Table] from [Stored Procedure]

MySQL How to INSERT INTO [temp table] FROM [Stored Procedure]

The problem is, Stored Procedures don't really return output directly. They can execute select statements inside the script, but have no return value.

MySQL calls stored procedures via CALL StoredProcedureName(); And you cannot direct that output to anything, as they don't return anything (unlike a function).

MySQL Call Command

create and insert into temporary table using mysql stored procedure

You need to use insert ... select rather than select ... into

INSERT INTO tempDesTable SELECT ID, FirstName, LastName FROM t_users 
WHERE `DesignationID` = p_DesignationID AND BranchID = p_BranchID;

Insert into temp table from stored procedure causes error

I ended creating two temp tables and inserting each stored procedure into each table and then joining them.

It worked perfectly for me:

ALTER Procedure [dbo].[summary_dashboard]
(
 @Period int,
 @DaysinMonth Int,
 @DayHrs Int
)
as
Declare  @Tbl1 as table  (SiteName nvarchar(50) null 
                    , Country nvarchar(50) null 
                    , BudgetPrj nvarchar(50) null 
                    , PeriodEnd DateTime null
                    , DaysMtd Int null 
                    , ToGoMtd Int null 
                    , PeriodToTDays Int null 
                    , MtdRev Numeric(13,2) null
                    , PrjRevenue Numeric(13,2) null 
                    , BdgRev Numeric(13,2) null 
                    , TrgRev Numeric(13,2) null 
                    , BCMMtd Numeric(13,2) null 
                    , HrsMtd Numeric(13,2) null 
                    , FuelVal Numeric(13,2) null 
                    , FuelLtrs Numeric(13,2) null
                    , FuelPerc Numeric(13,2) null 
                    )
Declare @Tbl2 as Table (SiteName nvarchar(50) null
                        , FltCnt Int null
                        , Availability Numeric (5,2) null
                        , Utilization Numeric (5,2) Null
                        , Idle Numeric(5,2)
                        )

insert into @Tbl1 (SiteName 
                    , Country 
                    , BudgetPrj 
                    , PeriodEnd 
                    , DaysMtd  
                    , ToGoMtd  
                    , PeriodToTDays  
                    , MtdRev 
                    , PrjRevenue 
                    , BdgRev  
                    , TrgRev 
                    , BCMMtd  
                    , HrsMtd 
                    , FuelVal 
                    , FuelLtrs 
                    , FuelPerc
                    )
Exec sp_Prd_Dashboard_Summary @Period

insert into @tbl2 (SiteName 
                        , FltCnt 
                , Availability 
                    , Utilization
                    , Idle 
                )
    Exec summary_fleet_performance @DayHrs, @Period, @DaysinMonth

    select tbl1.SiteName 
                    , tbl1.Country 
                    , tbl1.BudgetPrj 
                    , tbl1.PeriodEnd 
                    , tbl1.DaysMtd  
                    , tbl1.ToGoMtd  
                    , tbl1.PeriodToTDays  
                    , tbl1.MtdRev 
                    , tbl1.PrjRevenue 
                    , tbl1.BdgRev  
                    , tbl1.TrgRev 
                    , tbl1.BCMMtd  
                    , tbl1.HrsMtd 
                    , tbl1.FuelVal 
                    , tbl1.FuelLtrs 
                    , tbl1.FuelPerc
                    , tbl2.FltCnt 
                    , tbl2.Availability 
                    , tbl2.Utilization
                    , tbl2.Idle 

from @tbl1 tbl1 full outer join
     @tbl2 tbl2
on tbl1.SiteName = tbl2.SiteName

Insert stored procedure results into temp table

Hard to say without seeing the code to the stored procedure, but my guess is that the procedure also creates a temp table named #tmp. Try creating a temp table with a different name and running your INSERT EXEC into that, or post the code to the procedure so we can see it.

---

Related Topics