• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 935
  • Last Modified:

SELECT list contains fewer items than insert list ?

Hi.  What does this mean ?

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 have two tables.  One is simply:

site varchar(80)
score int

The other is has a whole ton of columns but I'm using:

site varchar(80)
caseage float
severity varchar(80)
cprlevel varchar(80)

There are many thousands of rows in this table but perhaps only a couple hundred unique sites.  For each unique site I want to run it through a formula and then store the site and the sum of the fomulas for that site as its own row in a new table.

I'm doing something along the lines of:

INSERT INTO newtable (Site, Score)
SELECT sitename as site, sum(myfunction(caseage,severity,cprlevel)) as Score
FROM bigtable
WHERE a bunch of conditions
GROUP BY site

When I try this query out in the analyzer I get this error:

Server: Msg 120, Level 15, State 1, Line 1
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.

As far as I can tell, I'm selecting two columns and inserting two columns.  What is this message trying to tell me ?  The entire piece I'm playing with is included below.  When I get this working I want to modify it to use a UNION query because there are many bigtable's out there, maybe do some ORDER BY score DESCs and change the SELECT to a TOP 25, etc, but first I need to get something really basic working.  Here it is:

declare @OutputTableName varchar(1024)
declare @ElevationsTable varchar(1024)
declare @Region varchar(80)
declare @SQLCmd varchar(80)

set @OutputTableName = '[CPRDB].[dbo].[tblCustomerSensitivity_AM]'
set @ElevationsTable = '[CPRDB].[dbo].[tblElevations]'
set @Region = 'AM'

set @SQLCmd =      'Insert into ' + @OutputTableName + ' (Site,Score) ' +
            'select ElevationSiteName as Site, ' +
            'Sum(slfn_ScoreCase(clcCaseAge,Severity,CaseMgmtLevel)) as Score ' +
            'from ' + @ElevationsTable + ' where L3Status NOT LIKE ''%closed%'' and ' +
            'WFMSource = ''' + @Region + ''' ' +
            'Group by Site'

print @SQLCmd
Execute (@SQLCmd)
0
_Scotch_
Asked:
_Scotch_
1 Solution
 
QPRCommented:
Have you tried just running the select part on the insert statement to see if you get what you expect?
Try running the code in QA rather than building and executing the sql string - does it work this time?
I can't see any problems with the code from this angle.
0
 
Christopher GordonSenior Developer AnalystCommented:
Not sure if this is your problem, but your example has @SQLCmd declared as a varchar(80) which is not a big enough varchar to hold the entire contents of your dynammic SQL. Try declaring it as a varchar(1000) maybe.
0
 
_Scotch_Author Commented:
Huh? #)($*# .. It is !  Nice catch.  I'll try that when I get back to the office.  Somebody get me a towel to wipe this egg off my face...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Aneesh RetnakaranDatabase AdministratorCommented:
>Sum(slfn_ScoreCase(clcCaseAge,Severity,CaseMgmtLevel)) as Score

What's this 'slfn_ScoreCase', i hope it is a function. the owner should be prefixed before the function call

SUM(dbo.slfn_ScoreCase(...))
0
 
imran_fastCommented:
I'm doing something along the lines of:

INSERT INTO newtable (Site, Score)
SELECT sitename as site, sum(myfunction(caseage,severity,cprlevel)) as Score
FROM bigtable
WHERE a bunch of conditions
GROUP BY site

please post the code for myfunction whcih is 'slfn_ScoreCase' i am sure something wrong in it which is giving you error.
0
 
QPRCommented:
Which is why I suggest he run the select (only) in QA.
I think gohord may have caught the cause of the problem.
Often you don't get the real cause of the error if it is buried in 2 layers (so to speak).
The error could reside in the function (for example) but by the time the insert is ready to happen there are not enough columns but the error hasn't bubbled up.
0
 
_Scotch_Author Commented:
gohord had it nailed.  As soon as I allocated enough space to SQLCmd and fixed a couple of typos the query did almost what I wanted.  Lazy error, but I'm disappointed it wasn't caught in more dramatic fashion.

Having trouble with the GROUP BY SITE part but technically a different question.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now