Can't figure out why The select list for the INSERT statement contains fewer items than the insert list

Question

Hi everyone,
I want to create a stored procedure that will create a table and return value, but I got this message:

Msg 120, Level 15, State 1, Procedure sp_Month01_FYCalendar, Line 36 [Batch Start Line 9]
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

I tried adding, deleting, removing and always the must match error, can someone please help, here is the SQL query:

USE [ACR_Accounting]  
GO  
  
/****** Object:  StoredProcedure [dbo].[sp_Month01_FYCalendar]    Script Date: 2022-11-11 5:15:35 PM ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE PROCEDURE [dbo].[sp_Month01_FYCalendar]  
    @startDate DATE, -- the start of the date range  
    @endDate   DATE -- the end of the date range  
AS  
BEGIN  
    SET NOCOUNT ON;  
  
    DECLARE @fiscalOffset int = -1;  
    if (@startDate > @endDate)  
    BEGIN  
        DECLARE @temp date = @startDate;  
        SET @startDate = @endDate;  
        SET @endDate = @temp;  
    END  
		    IF OBJECT_ID('dbo.Month01_FYCalendar') IS NOT NULL  
        DROP TABLE Month01_FYCalendar  
		CREATE TABLE Month01_FYCalendar   
		(  
CalendarID int identity(1,1) not null  
,FiscalDate date not null  
,FiscalYear int not null  
,FiscalMonth int not null  
,FiscalQuarter int not null  
,FiscalJulianDay int not null  
,MonthName nvarchar(15) not null  
,DayOfMonth int not null  
,WeekDayNumber int not null  
,WeekDayName nvarchar(15) not null  
		)  
		;WITH m AS   
    (  
        SELECT TOP (DATEDIFF(Month, @startDate, @endDate) + 1) m = ROW_NUMBER() OVER (ORDER BY [object_id])   
        FROM sys.all_objects  
    )  
    INSERT INTO Month01_FYCalendar   
    (  
--CalendarID  
FiscalDate  
,FiscalYear  
,FiscalMonth  
,FiscalQuarter  
,FiscalJulianDay  
,MonthName  
,DayOfMonth  
,WeekDayNumber  
,WeekDayName  
    )  
    SELECT CONVERT(VARCHAR, DATEPART(YEAR,  DATEADD(Month, m-1, @startDate))) + CONVERT(VARCHAR, DATEPART(MONTH, DATEADD(Month, m-1, @startDate))),  
           DATEDIFF(MONTH, @startDate, DATEADD(Month, m-1, @startDate)) + 1  
    FROM m;  
  
  
    ;WITH n AS   
    (  
      SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id])   
      FROM sys.all_objects  
    )  
    SELECT n as [rownumber]  
           ,DATEADD(DAY, n-1, @startDate) AS FiscalDate  
           ,DATEPART(YEAR, DATEADD(DAY, n-1, @startDate)) AS FiscalYear  
           ,DATEPART(MONTH, DATEADD(DAY, n-1, @startDate)) AS FiscalMonth  
           ,DATEPART(QUARTER, DATEADD(DAY, n-1, @startDate)) AS FiscalQuarter  
           ,DATEPART(DAYOFYEAR, DATEADD(DAY, n-1, @startDate)) As FiscalJulianDay  
           ,DATENAME(MONTH, DATEADD(DAY, n-1, @startDate)) AS [MonthName]  
           ,DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) AS [DayOfMonth]  
           ,DATEPART(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayNumber  
           ,DATENAME(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayName  
FROM n  
END  
  
GO  

Answer 1

CREATE PROCEDURE [dbo].[sp_Month01_FYCalendar]  
     @startDate DATE, -- the start of the date range  
     @endDate   DATE -- the end of the date range  
 AS  
 BEGIN  
     SET NOCOUNT ON;  
      
     DECLARE @fiscalOffset int = -1;  
     if (@startDate > @endDate)  
     BEGIN  
         DECLARE @temp date = @startDate;  
         SET @startDate = @endDate;  
         SET @endDate = @temp;  
     END  
             IF OBJECT_ID('dbo.Month01_FYCalendar') IS NOT NULL  
         DROP TABLE Month01_FYCalendar  
         CREATE TABLE Month01_FYCalendar   
         (  
 CalendarID int identity(1,1) not null  
 ,  
 FiscalDate date not null  
 ,FiscalYear int not null  
 ,FiscalMonth int not null  
 ,FiscalQuarter int not null  
 ,FiscalJulianDay int not null  
 ,MonthName nvarchar(15) not null  
 ,DayOfMonth int not null  
 ,WeekDayNumber int not null  
 ,WeekDayName nvarchar(15) not null  
         )  
         ;WITH m AS   
     (  
   SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id])   
       FROM sys.all_objects  
     )  
     INSERT INTO Month01_FYCalendar   
     (  
 --CalendarID  
 FiscalDate  
 ,FiscalYear  
 ,FiscalMonth  
 ,FiscalQuarter  
 ,FiscalJulianDay  
 ,MonthName  
 ,DayOfMonth  
 ,WeekDayNumber  
 ,WeekDayName  
     )  
   
     SELECT DATEADD(DAY, n-1, @startDate) AS FiscalDate  
            ,DATEPART(YEAR, DATEADD(DAY, n-1, @startDate)) AS FiscalYear  
            ,DATEPART(MONTH, DATEADD(DAY, n-1, @startDate)) AS FiscalMonth  
            ,DATEPART(QUARTER, DATEADD(DAY, n-1, @startDate)) AS FiscalQuarter  
            ,DATEPART(DAYOFYEAR, DATEADD(DAY, n-1, @startDate)) As FiscalJulianDay  
            ,DATENAME(MONTH, DATEADD(DAY, n-1, @startDate)) AS [MonthName]  
            ,DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) AS [DayOfMonth]  
            ,DATEPART(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayNumber  
            ,DATENAME(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayName  
 FROM m  
 END  
      
 GO  
  

Answer 2

Hi @Claude Larocque
As the error message indicates, the select list for the INSERT statement contains fewer items (which is 2 in your code) than the insert list (which is 9 in your code).

You need to ensure that the number of SELECT values must match the number of INSERT columns.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.