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)
LVL 3
_Scotch_Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_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...
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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(...))
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.
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.
_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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.