CODEWITHSUNDEEP
sqlsql-servert-sql
Chakra
Chakra

Reputation: 2557

Why do i get this error when i do a SELECT..INSERT in SQL Server

Why do i get this error

The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

When i run this query 

INSERT INTO TempOutputOfGroupifySP
    (MonthOfQuery,Associate,[NoOfClaims],[ActualNoOfLines],[AverageTATInDays],
    [NoOfErrorsDiscovered],[VarianceinPercent],[NoOfClaimsAudited],[InternalQualInPercent],[ExternalQualInPercent]
    )

SELECT (DATENAME(MONTH,[ClaimProcessedDate])) AS MonthOfQuery,
    Temp.Associate AS Associate,
    COUNT(*) AS [NoOfClaims],
    SUM(NoOfLines) AS [ActualNoOfLines] ,
    (SUM(DATEDIFF(dd,[ClaimReceivedDate],[ClaimProcessedDate]))/COUNT(*))  AS [AverageTATInDays],
    A.[NoOfErrorsDiscovered] AS [NoOfErrorsDiscovered],
    Temp.[MonthlyTarget] As [TargetNoOfLines],(Temp.[MonthlyTarget] - COUNT(*)) AS [VarianceInPercent],
    B.[NoOfClaimsAudited] AS [NoOfClaimsAudited],
    ((A.[NoOfErrorsDiscovered]/NULLIF(B.[NoOfClaimsAudited],0))*100) AS [InternalQualInPercent],
    NULL AS [ExternalQualInPercent]
    FROM 
    (SELECT COUNT(*) AS [NoOfErrorsDiscovered] FROM TempTableForStatisticsOfAssociates T1 WHERE [TypeOfError] IS NOT NULL) AS A, 
    (SELECT COUNT(*) AS [NoOfClaimsAudited] FROM TempTableForStatisticsOfAssociates T2 WHERE Auditor IS NOT NULL) AS B,
    TempTableForStatisticsOfAssociates Temp
    GROUP BY DATENAME(MONTH,([ClaimProcessedDate])),
    Temp.Associate,
    A.[NoOfErrorsDiscovered],
    Temp.[MonthlyTarget],
    B.[NoOfClaimsAudited]

Strucuture of the target table is 

CREATE TABLE [dbo].[TempOutputOfGroupifySP](
    [MonthOfQuery] [nchar](10) NULL,
    [Associate] [nvarchar](max) NULL,
    [NoOfClaims] [int] NULL,
    [ActualNoOfLines] [int] NULL,
    [AverageTATInDays] [int] NULL,
    [NoOfErrorsDiscovered] [int] NULL,
    [VarianceInPercent] [float] NULL,
    [NoOfClaimsAudited] [int] NULL,
    [InternalQualInPercent] [float] NULL,
    [ExternalQualInPercent] [float] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Upvotes: 0

Views: 63

Answers (1)

user3524542
user3524542

Reputation:

Your INSERT INTO defines 10 colums for the insertion, however, your SELECT statement return 11 columns. You are either missing a column in your INSERT statement or returning one too many in your SELECT statement. Comparing your table structure and your SELECT and INSERT the following line in your SELECT statement doesn't have a counterpart:

Temp.[MonthlyTarget] As [TargetNoOfLines]

Upvotes: 2

Related Questions