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.

ahmed salah 3,131 Reputation points
2021-11-04T08:14:29.353+00:00

i work on sql server 2012
i get error as below:
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.

statement generate error

  INSERT INTO [ExtractReports].dbo.AllData(PartID,Code,CodeTypeID,RevisionID,ZPLID,FeatureName,FeatureValue,ConCount) SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID, STUFF((SELECT  + CAST(d.ColumnName AS VARCHAR(300)) AS [text()]
                 FROM [ExtractReports].dbo.PartAttributes t1 with(nolock)
                  inner join core_datadefinitiondetails d with(nolock) on t1.ZfeatureKey=d.columnnumber
               WHERE t1.PartId = PM.PartId 
               FOR XML PATH(')), 1, 1, NULL) AS Featurekey,
             STUFF((SELECT  + CAST(O.Name AS VARCHAR(300)) AS [text()]
               FROM [ExtractReports].dbo.PartAttributes  AS O
               WHERE O.PartId = PM.PartId 
               FOR XML PATH(')), 1, 1, NULL) AS FeatureValue,Count(1) as ConCount
                    FROM 
                    [ExtractReports].dbo.PartAttributes PM 
                    INNER JOIN  [ExtractReports].dbo.Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey Where (1=1 and   (PM.ZfeatureKey= 1501170111 And Name ='Zener') ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID  Having Count(1)>= 1

so why this statement generate that error
How to solve issue

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,516 questions
{count} votes

2 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,116 Reputation points Microsoft Employee
    2021-11-04T08:17:59.783+00:00

    Hi @ahmed salah ,

    Please have a try to replace all FOR XML PATH(')) with FOR XML PATH('')).

    If it is not working, please provide the DDL and sample data.

    Thank you for understanding!

    Best regards,
    Melissa


    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.

    No comments

  2. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2021-11-04T22:28:54.45+00:00

    You have eight columns in the INSERT list. You have six columns in the SELECT list.

    How to solve it? Well, the columns FeatureName and FeatureValue in the INSERT list are the ones that seems to missing matching columns in the SELECT list, so try removing them.

    No comments