Recieving the error "Column name or number of supplied values does not match table definition"?

Question

I am getting this error only after adding 2 columns (Message and ErrorId) to 2 temp tables. Everything in this MS Report query worked fine before adding this.

Edit: This ended up being a bad question because the error message explains what the issue is. I ended up figuring it out but was innitially confused by the error. I would redact this post but I can't.

I only added Message and ErrorId columns to this temp load table

DECLARE @LoadsTbl TABLE
(
     LoadId     varchar(52),
     Message    varchar(Max),    -- after adding this it errors
     ErrorId    varchar(20)      -- after adding this it errors
)

DECLARE @SelLoadsTbl TABLE
(
     SourceSystem varchar(20),
     Message      varchar(Max),  -- after adding this it errors
     ErrorId      varchar(20),   -- after adding this it errors
     LoadId       varchar(52)
)

Everything below is what I am adding to.

DECLARE
    @Sep             CHAR(1),
    @PkupFromDttemp  DATETIME,
    @PkupToDttemp    DATETIME,
    @LoadNbrtemp     VARCHAR(20),
    @InvoiceNbrtemp  VARCHAR(20)

SET @PkupFromDttemp = @PkupFromDt
SET @PkupToDttemp = @PkupToDt
SET @LoadNbrtemp = @LoadNbr
SET @InvoiceNbrtemp = @InvoiceNbr
SET @Sep = ';'

SET @PkupToDttemp = dateadd(day, 1, @PkupToDttemp )

IF @CutoffTime IS NOT NULL
AND @CutoffTime <> '00:00'
    BEGIN
    SET @PkupFromDttemp = cast((etopsuser.fnDateOnly(@PkupFromDttemp) + ' ' + @CutoffTime ) as datetime)
    SET @PkupToDttemp = cast((etopsuser.fnDateOnly(@PkupToDttemp) + ' ' + @CutoffTime ) as datetime)
    END

    IF @LoadNbrtemp IS NOT NULL
    BEGIN
    SET @LoadNbrtemp = rtrim(ltrim(@LoadNbrtemp))

    IF charindex('%',@LoadNbrtemp) = 0
    BEGIN
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    WHERE fpl.LoadNbr = @LoadNbrtemp
    END
    ELSE
    BEGIN

    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl  WITH (NOLOCK)
    WHERE fpl.LoadNbr LIKE @LoadNbrtemp
    ORDER BY fpl.LoadId

    SET ROWCOUNT 0
    END
    END
    ELSE IF @InvoiceNbrtemp IS NOT NULL
    BEGIN
    SET @InvoiceNbrtemp = rtrim(ltrim(@InvoiceNbrtemp))

    IF charindex('%',@InvoiceNbrtemp) = 0
    BEGIN
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN dbo.vwFPFrtInvoiceLoads vwinv WITH (NOLOCK)
    ON fpl.LoadId = vwinv.LoadId
    WHERE vwinv.InvoiceNbr = @InvoiceNbrtemp
    END
    ELSE
    BEGIN

    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN dbo.vwFPFrtInvoiceLoads vwinv WITH (NOLOCK)
    ON fpl.LoadId = vwinv.LoadId
    WHERE vwinv.InvoiceNbr LIKE @InvoiceNbrtemp
    ORDER BY fpl.LoadId
    
    SET ROWCOUNT 0
    END
    END
    ELSE IF @InvoiceStatusList IS NULL
    AND @CarrierIdList IS NULL
    AND @ScacList IS NULL
    BEGIN
    -- no invoice filters
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN etopsuser.fnSecurUserAppOwners(@UserId,'FP') uo
      ON fpl.OwnerId = uo.OwnerId
    WHERE (@OwnerId IS NULL OR fpl.OwnerId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@OwnerId,@Sep)) )
    AND ( @PkupFromDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) >= @PkupFromDttemp )
    AND ( @PkupToDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) < @PkupToDttemp )
    AND ( @SourceSystemList IS NULL OR fpl.SourceSystem IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@SourceSystemList,@Sep)) )
    AND ( @MovementStatusList IS NULL OR fpl.MovementStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementStatusList,@Sep)) )
    AND ( @FPStatusList IS NULL OR fpl.FPStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@FPStatusList,@Sep)) )
    AND ( @RateStatusList IS NULL OR fpl.RateStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@RateStatusList,@Sep)) )
    AND ( @MovementTypeList IS NULL OR fpl.MovementType IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementTypeList,@Sep)) )
    END
    ELSE
    BEGIN
    -- invoice filters - non statement bill
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN etopsuser.fnSecurUserAppOwners(@UserId,'FP') uo
    ON fpl.OwnerId = uo.OwnerId
    INNER JOIN dbo.FPLoadTenderedSCACs lts WITH (NOLOCK)
    ON fpl.LoadId = lts.LoadId AND lts.ActiveInd = 'Y'
    INNER JOIN dbo.CMSCAC cms WITH (NOLOCK)
    ON lts.SCAC = cms.SCAC
    LEFT OUTER JOIN dbo.FPFrtInvoice inv WITH (NOLOCK)
    ON fpl.LoadId = inv.LoadId AND inv.BillType <> 'STATEMENT BILL'
    WHERE (@OwnerId IS NULL OR fpl.OwnerId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@OwnerId,@Sep)) )
    AND ( @PkupFromDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) >= @PkupFromDttemp )
    AND ( @PkupToDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) < @PkupToDttemp )
    AND ( @SourceSystemList IS NULL OR fpl.SourceSystem IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@SourceSystemList,@Sep)) )
    AND ( @MovementStatusList IS NULL OR fpl.MovementStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementStatusList,@Sep)) )
    AND ( @FPStatusList IS NULL OR fpl.FPStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@FPStatusList,@Sep)) )
    AND ( @RateStatusList IS NULL OR fpl.RateStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@RateStatusList,@Sep)) )
    AND ( @MovementTypeList IS NULL OR fpl.MovementType IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementTypeList,@Sep)) )
    AND ( @InvoiceStatusList IS NULL OR inv.InvoiceStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@InvoiceStatusList,@Sep)) )
    AND ( @CarrierIdList IS NULL OR cms.CarrierId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@CarrierIdList,@Sep)) )
    AND ( @ScacList IS NULL OR lts.Scac IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@ScacList,@Sep)) )

    -- invoice filters - statement bill
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN etopsuser.fnSecurUserAppOwners(@UserId,'FP') uo
    ON fpl.OwnerId = uo.OwnerId
    INNER JOIN dbo.FPLoadTenderedSCACs lts WITH (NOLOCK)
    ON fpl.LoadId = lts.LoadId AND lts.ActiveInd = 'Y'
    INNER JOIN dbo.CMSCAC cms WITH (NOLOCK)
    ON lts.SCAC = cms.SCAC
    INNER JOIN dbo.FPFrtInvoiceSBLoads sb WITH (NOLOCK)
    ON fpl.LoadId = sb.LoadId
    LEFT OUTER JOIN dbo.FPFrtInvoice inv WITH (NOLOCK)
    ON sb.InvoiceId = inv.InvoiceId AND inv.BillType = 'STATEMENT BILL'
    WHERE (@OwnerId IS NULL OR fpl.OwnerId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@OwnerId,@Sep)) )
    AND ( @PkupFromDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) >= @PkupFromDttemp )
    AND ( @PkupToDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) < @PkupToDttemp )
    AND ( @SourceSystemList IS NULL OR fpl.SourceSystem IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@SourceSystemList,@Sep)) )
    AND ( @MovementStatusList IS NULL OR fpl.MovementStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementStatusList,@Sep)) )
    AND ( @FPStatusList IS NULL OR fpl.FPStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@FPStatusList,@Sep)) )
    AND ( @RateStatusList IS NULL OR fpl.RateStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@RateStatusList,@Sep)) )
    AND ( @MovementTypeList IS NULL OR fpl.MovementType IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementTypeList,@Sep)) )
    AND ( @InvoiceStatusList IS NULL OR inv.InvoiceStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@InvoiceStatusList,@Sep)) )
    AND ( @CarrierIdList IS NULL OR cms.CarrierId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@CarrierIdList,@Sep)) )
    AND ( @ScacList IS NULL OR lts.Scac IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@ScacList,@Sep)) )
END
    
    --ME

    --EndChange


INSERT INTO @SelLoadsTbl
SELECT fpl.SourceSystem, fpl.LoadId
FROM @LoadsTbl tmp
INNER JOIN dbo.FPLoads fpl WITH (NOLOCK)
    ON tmp.LoadId = fpl.LoadId
WHERE fpl.FPStatus <> 'DO NOT PAY'
ORDER BY
     fpl.SourceSystem
    ,fpl.LoadId

SELECT DISTINCT
     fpl.SourceSystem as SourceSystem
    ,fpl.LoadId as LoadId
    ,etopsuser.fnFPLoadTenderedScac(fpl.LoadId) as Scac
    ,fpl.MovementType as MovementType
    ,cast(etopsuser.fnDateOnly(isnull(fpl.PkupActlDtTm, fpl.PkupDtTm))as datetime) as PkupDt
    ,right(etopsuser.fnMilDate(isnull(fpl.PkupActlDtTm, fpl.PkupDtTm)),5) as PkupTm
    ,fpl.MovementStatus as MovementStatus
    ,fpl.FPStatus           as FPLoadStatus
    ,fpl.RateStatus         as RatingStatus
    ,etopsuser.fnFmtCityStateZip(st1.StopCity, st1.StopState, st1.StopZip) as 'Origin'
    ,etopsuser.fnFmtCityStateZip(st2.StopCity, st2.StopState, st2.StopZip) as 'Destination'
     
    
    ,null as InvoiceId
    ,null as InvoiceNbrSeq
    ,null as InvoiceStatus
    ,null as BillType
    
    
FROM @SelLoadsTbl tmp
INNER JOIN dbo.FPLoads fpl WITH (NOLOCK)
    ON tmp.LoadId = fpl.LoadId
INNER JOIN FPStops ST1 WITH (NOLOCK)
    ON fpl.LoadId = ST1.LoadId AND ST1.StopNbr = 1
INNER JOIN FPStops ST2 WITH (NOLOCK)
    ON fpl.LoadId = ST2.LoadId AND ST2.StopNbr = fpl.TotalStops
--WHERE (fpl.LoadId IN (SELECT LoadId FROM @LoadsTbl) )

union 

SELECT 
    tmp.SourceSystem as SourceSystem
    ,inv.LoadId as LoadID
    ,inv.BilledScac as Scac
    ,null as MovementType
    ,null as PkupDt
    ,null as PkupTm
    ,null as MovementStatus
    ,null           as FPLoadStatus
    ,null           as RatingStatus
    ,null as 'Origin'
    ,null as 'Destination'


    
    ,inv.InvoiceId as InvoiceId
    ,inv.InvoiceNbr + '-' + inv.InvoiceSeqNbr as InvoiceNbrSeq
    ,inv.InvoiceStatus as InvoiceStatus
    ,inv.BillType as BillType
    
FROM @SelLoadsTbl tmp
INNER JOIN dbo.FPFrtInvoice inv WITH (NOLOCK)
    ON tmp.LoadId = inv.LoadId
WHERE (inv.LoadId IN (SELECT LoadId FROM @SelLoadsTbl) )
AND inv.BillType <> 'STATEMENT BILL'
--
UNION
--
SELECT 
    tmp.SourceSystem  as SourceSystem
    ,sb.LoadId as LoadID
    --My Addition
    --Addition end
    ,inv.BilledScac as Scac
    ,null as MovementType
    ,null as PkupDt
    ,null as PkupTm
    ,null as MovementStatus
    ,null           as FPLoadStatus
    ,null           as RatingStatus
    ,null as 'Origin'
    ,null as 'Destination'
    
    ,inv.InvoiceId as InvoiceId
    ,inv.InvoiceNbr + '-' + inv.InvoiceSeqNbr as InvoiceNbrSeq
    ,inv.InvoiceStatus as InvoiceStatus
    ,inv.BillType as BillType
    
    
FROM @SelLoadsTbl tmp
INNER JOIN dbo.FPFrtInvoiceSBLoads sb WITH (NOLOCK)
ON tmp.LoadId = sb.LoadId
--me

--me end
INNER JOIN dbo.FPFrtInvoice inv WITH (NOLOCK)
    ON sb.InvoiceId = inv.InvoiceId AND inv.BillType = 'STATEMENT BILL'
WHERE (sb.LoadId IN (SELECT LoadId FROM @SelLoadsTbl) )
--
ORDER BY
     1, 2, 3
     
     
     

    

Answer 1

Your insert statements are probably the cause here. When you

INSERT INTO @LoadsTbl

without a list of columns - it expect all table columns to be supplied. You're only selecting LoadId. If you add:

INSERT INTO @LoadsTbl (LoadId)
...

or add placeholder values to your select statements for Message and ErrorId

INSERT INTO @LoadsTbl
SELECT LoadId, '' Message, '' ErrorId

That should solve this error.

Answer 2

Here's a nice way to write inserts that SQL Server supports.

It's generally a good idea to avoid writing inserts such that your SELECT clause must stay in total sync with the target DDL. Otherwise if you ever add a column to the table, everything you've got feeding in to it breaks. That's the error you are getting. The table has three fields, but you are only giving it one in the SELECT.

CREATE TABLE DEMO_SYNTAX
  ( Field1 VARCHAR(100),
    Field2 VARCHAR(1000)
  )

INSERT INTO DEMO_SYNTAX (Field2)
    SELECT 'dummy';
    
INSERT INTO DEMO_SYNTAX (Field1,Field2)
    SELECT 'dummy2','dummy3';