Insert into temp table from stored procedure causes error

Question

I have this stored procedure:

ALTER Procedure [dbo].[sp_Prd_Dashboard_Summary]
    (@Period AS INT)
AS
    SELECT 
        SiteName AS SiteName, 
        MAX(Country) AS Country, 
        BudgetPrj, 
        MAX(PeriodEnd) AS PeriodEnd, 
        MAX(DaysMtd) AS DaysMtd,
        MAX(ToGoMtd) AS ToGoMtd, 
        MAX(PeriodToTDays) AS PeriodToTDays,
        SUM(MTDRevenue) AS MtdRev, 
        SUM(MTDRevenue) / NULLIF(MAX(DaysMTD), 0) * MAX(PeriodToTDays) AS PrjRevenue,
        SUM(BdgRevenue) AS BdgRev, SUM(TrgRevenue) AS TrgRev,
        SUM(BCMMtd) AS BCMMtd, SUM(HrsMtd) AS HrsMTD, 
        SUM(FuelVal) AS FuelVal, SUM(FuelLtrs) AS FuelLtrs,
        SUM(FuelVal) / NULLIF(SUM(MTDRevenue), 0) AS FuelPerc
    FROM
        (SELECT
             St.SiteName as SiteName,
             St.Country as Country, 
             Null as BudgetPrj, Prd.PeriodEnd as PeriodEnd,
             Day(GetDate()) as DaysMtd, 
             Prd.PeriodNoDays - Day(GetDate()) as ToGoMtd,
             Prd.PeriodNoDays as PeriodToTDays,
             0 as MTDRevenue, 0 as BdgRevenue, 
             0 as TrgRevenue, 0 as BCMMtd,
             0 as HrsMtd, 0 as FuelVal,
             0 as FuelLtrs
         FROM 
             Periods Prd 
         JOIN
             Sites St ON Prd.PeriodSiteID = St.SiteId
         WHERE
             Prd.Period = @Period AND St.SiteActive = 1

         UNION All

    Select SiteName as SiteName
    , Dit.Country as Country
    , Null as BudgetPrj
    , Null as PeriodEnd
    , 0 DaysMtd
    , 0 as ToGoMtd
    , 0 as PeriodToTDays
    , IIF(Dit.Wcode = 101, 
            IIF(DiT.WBillMeth = 'Hours', DiT.Hrs * DiT.OpBill,
                IIF(DiT.WBillMeth = 'BCM', Loads * DiT.ModelSize * DiT.WBillRate,
                    IIF(DiT.WBillMeth = 'Cost Plus', (DiT.Hrs * (DiT.OwnBill + DiT.OpBill)) +
                        (DiT.ShiftHrs * DiT.EmpBill),0))),0) as MTDRevenue
    , 0 as BdgRevenue
    , 0 as TrgRevenue
    , IIF(DiT.WBillMeth = 'BCM', Loads * DiT.ModelSize, 0) as BCMMtd
    , IIF(Dit.Wcode = 101, 
            IIF(DiT.WBillMeth <> 'BCM', DiT.Hrs, 0),0) as HrsMtd
    , DiT.Fuel * DiT.FuelRate as FuelVal
    , DiT.Fuel as FuelLtrs

    From DataInputTotal DiT

    Where DiT.Period = @Period and DiT.SiteActive = 1

    Union All

    Select SiteName as SiteName
    , St.Country as Country
    , Bdgt.BudgetProject as BudgetPrj
    , Prd.PeriodEnd as PeriodEnd
    , 0 as DaysMtd
    , 0 as ToGoMtd
    , 0 as PeriodToTDays
    , 0 as MTDRevenue
    , PrjRev as BdgRevenue
    , BudgTarget as TrgRevenue
    , 0 as BCMMtd
    , 0 as HrsMtd
    , 0 as FuelVal
    , 0 as FuelLtrs

    From Budget Bdgt Join 
        Sites St on Bdgt.SiteId = St.SiteId Join
        Periods Prd on Bdgt.Period = prd.Period and Bdgt.SiteId = Prd.PeriodSiteID

    Where Bdgt.Period = @Period and St.SiteActive = 1

) a


Group By SiteName, BudgetPrj

I am trying to call the procedure and insert the result into a temp table with the below script:

Declare @Period int = 22
Declare @DaysinMonth Int = 29
Declare @DayHrs Int = 24

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

Create Table #Temp (SiteName nvarchar(50)
                    , Country nvarchar(50)
                    , BudgetPrj nvarchar(50)
                    , PeriodEnd DateTime
                    , DaysMtd Int
                    , ToGoMtd Int
                    , PeriodToTDays Int
                    , MtdRev Numeric(13,2)
                    , PrjRevenue Numeric(13,2)
                    , BdgRev Numeric(13,2)
                    , TrgRev Numeric(13,2)
                    , BCMMtd Numeric(13,2)
                    , HrsMtd Numeric(13,2)
                    , FuelVal Numeric(13,2)
                    , FuelLtrs Numeric(13,2)
                    , FuelPerc Numeric(13,2)
                    , FltCnt Int
                    , Availibility Numeric(13,2)
                    , Utilization Numeric(13,2)
                    , Idle Numeric(13,2)
                    )

Insert #temp
Exec sp_Prd_Dashboard_Summary @Period
Insert into #temp
Exec summary_fleet_performance @DayHrs, @Period, @DaysinMonth

When running the script I get the following error:

Column name or number of supplied values does not match table definition.

I have checked the aliases of the SELECT and all columns have names.

Answer 1

The temporary table to which you are trying to insert rows contains more columns than the result from stored procedure. Provide column names in insert query, like this:

Insert into table(Column1, Column2....)

Answer 2

TRY THIS: Your stored procedure is returning 14 columns and temporary table has more than that so you have to mention columnS in the INSERT INTO #TEMP as below and if you are not specifying the columns name of table then returning columns from the STORED PROCEDURE also must be same.

INSERT INTO #temp(SiteName
    , Country
    , BudgetPrj
    , PeriodEnd
    , DaysMtd
    , ToGoMtd
    , PeriodToTDays
    , MtdRev
    , PrjRevenue
    , BdgRev
    , TrgRev
    , BCMMtd
    , HrsMtd
    , FuelVal
    , FuelLtrs)
Exec sp_Prd_Dashboard_Summary @Period

Answer 3

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