SQL Merge - Number of Colums on Insert Must Match Number of Columns Specified

Question

I am working with a stored procedure where I am using a Merge statement to do an insert or an update based on the conditions.

However when trying to compile the procedure I get the error

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

However, I do believe that I have got the right number of columns, given that it was a copy and paste exercise from the insert into the values area.

Please see the Merge statement below and let me know how I am going wrong on this please

WITH CTE AS 
(
    SELECT  
        [PRNCPL_ACC_CD], [BRANCH_ACC_CD],
        [COPY_MISC_DLR_FG], [COPY_LABR_HRS_FG],
        [COPY_CSUM_COST_FG], [COPY_PART_PRICE_FG],
        [COPY_TRVL_RATE_FG], [COPY_DISC_FG],        
        [COPY_TAX_RATE_FG], [COPY_REJ_RESN_FG],
        [COPY_CNTR_OBLG_FG], [COPY_LABR_OVRS_FG]
    FROM 
        CEQBP 
    WHERE  
        SHEDDULED_CHANGE_ID = CONVERT(CHAR(255), @SHEDDULED_CHANGE_ID)
)
MERGE INTO CEQBM AS T 
USING CTE S ON  T.PRNCPL_ACC_CD  = S.PRNCPL_ACC_CD
            AND T.BRANCH_ACC_CD = S.BRANCH_ACC_CD

WHEN MATCHED THEN
    UPDATE 
       SET 
          T.[PRNCPL_ACC_CD] = S.[PRNCPL_ACC_CD],
          T.[BRANCH_ACC_CD] = S.[BRANCH_ACC_CD],
          T.[COPY_MISC_DLR_FG] = S.[COPY_MISC_DLR_FG],
          T.[COPY_LABR_HRS_FG] = S.[COPY_LABR_HRS_FG],
          T.[COPY_CSUM_COST_FG] = S.[COPY_CSUM_COST_FG],
          T.[COPY_PART_PRICE_FG] = S.[COPY_PART_PRICE_FG],
          T.[COPY_TRVL_RATE_FG] = S.[COPY_TRVL_RATE_FG],
          T.[COPY_DISC_FG] = S.[COPY_DISC_FG],
          T.[COPY_TAX_RATE_FG] = S.[COPY_TAX_RATE_FG],
          T.[COPY_REJ_RESN_FG] = S.[COPY_REJ_RESN_FG], 
          T.[COPY_CNTR_OBLG_FG] = S.[COPY_CNTR_OBLG_FG],
          T.[COPY_LABR_OVRS_FG] = S.[COPY_LABR_OVRS_FG]

WHEN NOT MATCHED THEN 
    INSERT ([PRNCPL_ACC_CD], [BRANCH_ACC_CD],
            [COPY_MISC_DLR_FG], [COPY_LABR_HRS_FG],
            [COPY_CSUM_COST_FG], [COPY_PART_PRICE_FG],
            [COPY_TRVL_RATE_FG], [COPY_DISC_FG],
            [COPY_TAX_RATE_FG], [COPY_REJ_RESN_FG],
            [COPY_CNTR_OBLG_FG], [COPY_LABR_OVRS_FG])
    VALUES (S.[PRNCPL_ACC_CD], S.[BRANCH_ACC_CD],
            S.[COPY_MISC_DLR_FG], S.[COPY_LABR_HRS_FG],
            S.[COPY_CSUM_COST_FG], S.[COPY_PART_PRICE_FG,
            S.[COPY_TRVL_RATE_FG], S.[COPY_DISC_FG],
            S.[COPY_TAX_RATE_FG], S.[COPY_REJ_RESN_FG],
            S.[COPY_CNTR_OBLG_FG], S.[COPY_LABR_OVRS_FG]);

Edit with full scripts

User Defined Table Type

CREATE TYPE CEQBP_TYP AS TABLE(
    [PRNCPL_ACC_CD]         [char](10)                  ,
    [BRANCH_ACC_CD]         [char](10)                  ,
    [COPY_MISC_DLR_FG]      BIT DEFAULT(0)              ,
    [COPY_LABR_HRS_FG]      BIT DEFAULT(0)              ,
    [COPY_CSUM_COST_FG]     BIT DEFAULT(0)              ,
    [COPY_PART_PRICE_FG]    BIT DEFAULT(0)              ,
    [COPY_TRVL_RATE_FG]     BIT DEFAULT(0)              ,
    [COPY_DISC_FG]          BIT DEFAULT(0)              ,
    [COPY_TAX_RATE_FG]      BIT DEFAULT(0)              ,
    [COPY_REJ_RESN_FG]      BIT DEFAULT(0)              ,
    [COPY_CNTR_OBLG_FG]     BIT DEFAULT(0)              ,
    [COPY_LABR_OVRS_FG]     BIT DEFAULT(0)              ,
    [EDIT_MISC_DLR_FG]      BIT DEFAULT(0)              ,
    [EDIT_LABR_HRS_FG]      BIT DEFAULT(0)              ,
    [EDIT_CSUM_COST_FG]     BIT DEFAULT(0)              ,
    [EDIT_PART_PRICE_FG]    BIT DEFAULT(0)              ,
    [EDIT_TRVL_RATE_FG]     BIT DEFAULT(0)              ,
    [EDIT_DISC_FG]          BIT DEFAULT(0)              ,
    [EDIT_TAX_RATE_FG]      BIT DEFAULT(0)              ,
    [EDIT_REJ_RESN_FG]      BIT DEFAULT(0)              ,
    [EDIT_CNTR_OBLG_FG]     BIT DEFAULT(0)              ,
    [EDIT_LABR_OVRS_FG]     BIT DEFAULT(0)              ,
    [SHEDULED_BY]           VARCHAR (10)                ,
    [DATE_SCHEDULED]        DATETIME DEFAULT(GETDATE()) ,
    [EXECUTE_IMMEDIATELY]   DATETIME DEFAULT(1)         
)
GO

Stored Procedure

CREATE PROCEDURE sp_InsertCopyDataStagingData

@TVP CEQBP_TYP READONLY 

AS
BEGIN
BEGIN TRY
BEGIN TRAN

DECLARE @SHEDDULED_CHANGE_ID uniqueidentifier = NEWID()

INSERT INTO CEQBP (
                     [SHEDDULED_CHANGE_ID]  
                    ,[PRNCPL_ACC_CD]            
                    ,[BRANCH_ACC_CD]            
                    ,[COPY_MISC_DLR_FG]     
                    ,[COPY_LABR_HRS_FG]     
                    ,[COPY_CSUM_COST_FG]        
                    ,[COPY_PART_PRICE_FG]   
                    ,[COPY_TRVL_RATE_FG]        
                    ,[COPY_DISC_FG]         
                    ,[COPY_TAX_RATE_FG]     
                    ,[COPY_REJ_RESN_FG]     
                    ,[COPY_CNTR_OBLG_FG]        
                    ,[COPY_LABR_OVRS_FG]        
                    ,[EDIT_MISC_DLR_FG]     
                    ,[EDIT_LABR_HRS_FG]     
                    ,[EDIT_CSUM_COST_FG]        
                    ,[EDIT_PART_PRICE_FG]   
                    ,[EDIT_TRVL_RATE_FG]        
                    ,[EDIT_DISC_FG]         
                    ,[EDIT_TAX_RATE_FG]     
                    ,[EDIT_REJ_RESN_FG]     
                    ,[EDIT_CNTR_OBLG_FG]        
                    ,[EDIT_LABR_OVRS_FG]        
                    ,[SHEDULED_BY]          
                    ,[DATE_SCHEDULED]       
                    ,[EXECUTE_IMMEDIATELY]) 
SELECT
                     CONVERT(char(255), @SHEDDULED_CHANGE_ID) 
                    ,[PRNCPL_ACC_CD]            
                    ,[BRANCH_ACC_CD]            
                    ,[COPY_MISC_DLR_FG]     
                    ,[COPY_LABR_HRS_FG]     
                    ,[COPY_CSUM_COST_FG]        
                    ,[COPY_PART_PRICE_FG]   
                    ,[COPY_TRVL_RATE_FG]        
                    ,[COPY_DISC_FG]         
                    ,[COPY_TAX_RATE_FG]     
                    ,[COPY_REJ_RESN_FG]     
                    ,[COPY_CNTR_OBLG_FG]        
                    ,[COPY_LABR_OVRS_FG]        
                    ,[EDIT_MISC_DLR_FG]     
                    ,[EDIT_LABR_HRS_FG]     
                    ,[EDIT_CSUM_COST_FG]        
                    ,[EDIT_PART_PRICE_FG]   
                    ,[EDIT_TRVL_RATE_FG]        
                    ,[EDIT_DISC_FG]         
                    ,[EDIT_TAX_RATE_FG]     
                    ,[EDIT_REJ_RESN_FG]     
                    ,[EDIT_CNTR_OBLG_FG]        
                    ,[EDIT_LABR_OVRS_FG]        
                    ,[SHEDULED_BY]          
                    ,[DATE_SCHEDULED]       
                    ,[EXECUTE_IMMEDIATELY]
FROM @TVP

IF (SELECT EXECUTE_IMMEDIATELY FROM CEQBP WHERE  SHEDDULED_CHANGE_ID = CONVERT(char(255), @SHEDDULED_CHANGE_ID) ) = 1
BEGIN -- CALL STORED PROC TO RUN IMMEDIDATELY 
    WITH CTE AS (SELECT  
                 [PRNCPL_ACC_CD]        
                ,[BRANCH_ACC_CD]        
                ,[COPY_MISC_DLR_FG]
                ,[COPY_LABR_HRS_FG]
                ,[COPY_CSUM_COST_FG]
                ,[COPY_PART_PRICE_FG]
                ,[COPY_TRVL_RATE_FG]
                ,[COPY_DISC_FG]     
                ,[COPY_TAX_RATE_FG]
                ,[COPY_REJ_RESN_FG]
                ,[COPY_CNTR_OBLG_FG]
                ,[COPY_LABR_OVRS_FG]
                 FROM CEQBP WHERE  
                 SHEDDULED_CHANGE_ID = CONVERT(char(255), @SHEDDULED_CHANGE_ID))
    MERGE CEQBM AS T 
    USING CTE S ON  T.PRNCPL_ACC_CD  = S.PRNCPL_ACC_CD
                AND T.BRANCH_ACC_CD = S.BRANCH_ACC_CD
    WHEN MATCHED THEN
                UPDATE SET 
                 T.[PRNCPL_ACC_CD]      = S.[PRNCPL_ACC_CD]     
                ,T.[BRANCH_ACC_CD]      = S.[BRANCH_ACC_CD]     
                ,T.[COPY_MISC_DLR_FG]   = S.[COPY_MISC_DLR_FG]  
                ,T.[COPY_LABR_HRS_FG]   = S.[COPY_LABR_HRS_FG]  
                ,T.[COPY_CSUM_COST_FG]  = S.[COPY_CSUM_COST_FG] 
                ,T.[COPY_PART_PRICE_FG] = S.[COPY_PART_PRICE_FG]    
                ,T.[COPY_TRVL_RATE_FG]  = S.[COPY_TRVL_RATE_FG] 
                ,T.[COPY_DISC_FG]       = S.[COPY_DISC_FG]      
                ,T.[COPY_TAX_RATE_FG]   = S.[COPY_TAX_RATE_FG]  
                ,T.[COPY_REJ_RESN_FG]   = S.[COPY_REJ_RESN_FG]  
                ,T.[COPY_CNTR_OBLG_FG]  = S.[COPY_CNTR_OBLG_FG] 
                ,T.[COPY_LABR_OVRS_FG]  = S.[COPY_LABR_OVRS_FG]
    WHEN NOT MATCHED THEN 
                INSERT 
                    ( [PRNCPL_ACC_CD]       
                     ,[BRANCH_ACC_CD]       
                     ,[COPY_MISC_DLR_FG]    
                     ,[COPY_LABR_HRS_FG]    
                     ,[COPY_CSUM_COST_FG]   
                     ,[COPY_PART_PRICE_FG]  
                     ,[COPY_TRVL_RATE_FG]   
                     ,[COPY_DISC_FG]        
                     ,[COPY_TAX_RATE_FG]    
                     ,[COPY_REJ_RESN_FG]        
                     ,[COPY_CNTR_OBLG_FG]   
                     ,[COPY_LABR_OVRS_FG]   )
                VALUES
                    (
                      S.[PRNCPL_ACC_CD]     
                     ,S.[BRANCH_ACC_CD]     
                     ,S.[COPY_MISC_DLR_FG]  
                     ,S.[COPY_LABR_HRS_FG]  
                     ,S.[COPY_CSUM_COST_FG] 
                     ,S.[COPY_PART_PRICE_FG 
                     ,S.[COPY_TRVL_RATE_FG] 
                     ,S.[COPY_DISC_FG]      
                     ,S.[COPY_TAX_RATE_FG]  
                     ,S.[COPY_REJ_RESN_FG]  
                     ,S.[COPY_CNTR_OBLG_FG] 
                     ,S.[COPY_LABR_OVRS_FG]     );

END 
-- Using while loop to clean up any records that may have had an issue
-- This should only ever be one record at this stage
WHILE(SELECT TOP 1 * FROM CEQBP WHERE DATE_EXECUTED IS NULL AND EXECUTE_IMMEDIATELY = 1) > 0
    BEGIN
        DECLARE @UserIdVar          VARCHAR(10)
        DECLARE @DealerCodeVar      VARCHAR(10)
        DECLARE @FromBranchVar      VARCHAR(10)
        WITH CTE AS (SELECT TOP 1 * FROM CEQBP WHERE DATE_EXECUTED IS NULL AND EXECUTE_IMMEDIATELY = 1)
        SELECT  @UserIdVar = SHEDULED_BY 
               ,@DealerCodeVar = PRNCPL_ACC_CD 
               ,@FromBranchVar = BRANCH_ACC_CD FROM CTE
        EXEC sp_CopyData 
    END
COMMIT TRAN

END TRY
BEGIN CATCH

ROLLBACK TRAN

DECLARE 
            @ErrorMessage NVARCHAR(4000),
            @ErrorSeverity INT,
            @ErrorState INT

SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

END CATCH
END
GO

when clicking on the error message to take me to the offending line is takes me to the line `WITH CTE AS (SELECT` — Simon Price, Aug 16 '17 at 13:34
@TabAlleman I have posted the scripts that you would need in order to create this — Simon Price, Aug 16 '17 at 14:02
Eagle Eye says, "Missing a close bracket!" `S.[COPY_CSUM_COST_FG], S.[COPY_PART_PRICE_FG,` 3rd line of insert values clause. Without it wouldn't the engine treat `,S.[COPY_PART_PRICE_FG ,S.[COPY_TRVL_RATE_FG]` as one value? — xQbert, Aug 16 '17 at 14:05
Your script does not recreate the issue. I get this when running your `CREATE PROCEDURE` script: "The module 'sp_InsertCopyDataStagingData' depends on the missing object 'sp_CopyData'. The module will still be created; however, it cannot run successfully until the object exists." But the stored procedure does successfully compile. — Tab Alleman, Aug 16 '17 at 14:08
@xQbert Interesting that I have pasted the CREATE PROCEDURE script with the missing close bracket, and commented out the `EXEC sp_CopyData` line, and I am able to successfully create the procedure. Kinda weird that my SSMS doesn't catch what should clearly be invalid syntax. — Tab Alleman, Aug 16 '17 at 14:17
As an experiment, I tried removing the comma before one of the lines in the VALUES clause, and it triggered an invalid syntax error. that is weird. Why would closing brackets be optional in my SSMS? I can remove the closing brackets from several columns, no effect. I can even add extra ones (`[ColumnName]]`), and still the proc compiles. — Tab Alleman, Aug 16 '17 at 14:19
@TabAlleman I'd suspect since there is a valid open [, anything before a ] is being treated as the column name. Thus the compiler can't find a syntax issue until it tries to perform the insert when it actually recognizes an invalid number of columns compared to select. but yea it is odd. (not sure this is right yet though; even if it is highly suspect!) — xQbert, Aug 16 '17 at 14:21
OP says he gets the error trying to compile. If it's actually a run-time error, then I don't have enough script to reproduce it obviously. The compiler should recognize the column count being off, because if it treats everything before the next closing ] as the column name, then it should be combining two columns into one and getting an imbalance from the INSERT list. — Tab Alleman, Aug 16 '17 at 14:22
oh wow. now I am confused gotta be a compabality/version difference in compilers. — xQbert, Aug 16 '17 at 14:23
Ahhh, I have a new suspicion that the syntax error is masked by the fact that I don't have a table `CEQBP` in my database. My intellisense finally just recognized that as an invalid object. BUT I am still able to create the proc, and I don't receive a warning about the missing table. — Tab Alleman, Aug 16 '17 at 14:31
@CodeWarrior so did the missing ] solve the error? Are we now troubleshooting why the compilers are different? (different question?) — xQbert, Aug 16 '17 at 16:16
I do receive an error about the missing table when I try to EXECUTE the procedure, but not when I try to CREATE it. Maybe OP actually means EXECUTE when he says "Compile". — Tab Alleman, Aug 16 '17 at 19:09
@TabAlleman, no I have the issue when I try and compile it and create the stored procedure not when I try and execute it — Simon Price, Aug 17 '17 at 14:20
@xQbert no, that did not solve the error. I still have the issue — Simon Price, Aug 17 '17 at 14:21
@CodeWarrior What version of SQL Server are you using? and what version of SSMS? — Tab Alleman, Aug 17 '17 at 14:24

SQL Merge - Number of Colums on Insert Must Match Number of Columns Specified

0 Answers0