question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked MelissaMa-msft commented

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 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-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Run the select statement without the insert and see what it returns.

0 Votes 0 ·

Hi @ahmedsalah-1628,

Could you please provide any update?

If all suggestions are not helpful, please kindly post CREATE TABLE statements for your tables together with INSERT statements with sample data. We also need to see the expected result of the sample.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ahmedsalah-1628,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.