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

Claude Larocque 666 Reputation points
2022-11-11T22:32:34.147+00:00

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  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,330 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Jingyang Li 5,891 Reputation points
    2022-11-14T17:02:14.747+00:00
    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  
      
    

1 additional answer

Sort by: Most helpful
  1. LiHongMSFT-4306 25,651 Reputation points
    2022-11-14T03:19:08.38+00:00

    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).
    259991-image.png
    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.