Solved

select list for the INSERT statement contains fewer items than the insert list

Posted on 2003-11-08
11
1,434 Views
Last Modified: 2008-02-01
I am trying to run an insert statement as part of a larger script.  The statement is:

insert into gld2kclsql.omegadirect.dbo.tbl_sc_prices
 (header_id
, cost
, tax
, term
, discount_amount
, total
, deductible
, price_type
, effort_id)
select
  m_id.header_id
, dt.cprice1
, dt.tax1
, 1
, dt.disc1
, dt.amnt1
, 0
, 'O'
, @id2
from
  (select
     header_id
   , contract
   from gld2kclsql.omegadirect.dbo.tbl_sc_header) m_id
, gld2kclsql.wpfileimp.dbo.data_transform1 dt
where
    dt.contractnum = m_id.contract
and dt.sub_load_id = @LOADID --** change to current load_id from
data_transform1
and dt.do_not_mail = 0

The error that keeps coming up is:

Server: Msg 120, Level 15, State 1, Line 439
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.
Server: Msg 8180, Level 16, State 1, Line 439
Statement(s) could not be prepared.

This statement was working fine yesterday.  Last night the database had to be backed up and restored to a new server which was also named gld2kclsql.  The @id2 is set above in the script and there are no intervening "go" statements that would null it out.  Why can't the 9 insert items all be seen?  Any ideas from anyone??  
0
Comment
Question by:kneebuck
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 9707983
Even if it were null, it would still count as an "item".  My guess, the restored database's structure doesn't match the old one.  If you don't explicitly list the columns on the insert, SQL Server assumes you are going to insert into every column.  Make sure you don't have more (or fewer) columns in the restored database.

Also, for easier maintenance, it's better to list the columns you're going to insert into.

Brett
0
 

Author Comment

by:kneebuck
ID: 9708088
The table being inserted (tbl_sc_prices) has many more columns than what are listed on the insert list and the ones not mentioned either have specified defaults or are nullable.  The listing after the insert keyword are the fields that the select list is supposed to satisfy
0
 
LVL 34

Expert Comment

by:arbert
ID: 9708208
Doesn't matter if there are defaults or nulls--how does the insert statement know what fields you want to match up with?????  If you don't name the columns, you have to have "place holders" for each column in the insert statement.....

Brett
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 34

Expert Comment

by:arbert
ID: 9708220
Here's the post from books online:

The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into.
0
 

Author Comment

by:kneebuck
ID: 9708251
Looks to me like I have one-to-one corresondence:

insert ....
 (header_id
, cost
, tax
, term
, discount_amount
, total
, deductible
, price_type
, effort_id)
select
  m_id.header_id
, dt.cprice1
, dt.tax1
, 1
, dt.disc1
, dt.amnt1
, 0
, 'O'
, @id2


which item from the insert statement is not satisfied by an item from the select list?
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9708996
9 <--> 9

Please substitute for variable and run this query and see if 9 columns are returned. If datatypes do not match or not inserting into a not nullable column we should be getting a different error message.

select
  m_id.header_id
, dt.cprice1
, dt.tax1
, 1
, dt.disc1
, dt.amnt1
, 0
, 'O'
, @id2
from
  (select
     header_id
   , contract
   from gld2kclsql.omegadirect.dbo.tbl_sc_header) m_id
, gld2kclsql.wpfileimp.dbo.data_transform1 dt
where
    dt.contractnum = m_id.contract
and dt.sub_load_id = @LOADID --** change to current load_id from
data_transform1
and dt.do_not_mail = 0
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9719619
So what was the outcome to namasi_navaretnam question?  Were there 9 columns after all?

Anthony
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9722003
Table tbl_sc_prices may have an insert trigger associated with it. If an insert trigger exists check to if there are any insert statements within the trigger.

HTH
0
 

Author Comment

by:kneebuck
ID: 9723969
There definitely are nine columns.  However, there is a trigger on one of the tables that has an insert statement.  Would that null the @ID variable?
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 500 total points
ID: 9724511
if @id2 is null then with that the trigger inserted table will have null value for effor_id column. If you post the insert trigger we can take a look.

I think it is an issue within the trigger and something fails in trigger.

Within trigger after each sql statement add lines

If @@ERROR <> 0
BEGIN
   RAISERROR('Insert into MyTable Failed etc' , 16, 1)
   RETURN -1
END

If a error is raised then we can find out which statemnt raise error and work on resolving that issue.
0
 

Author Comment

by:kneebuck
ID: 9726558
Mgt got really concerned about this and so we called in a Microsoft tech who told us that the @@identity function was far more global than we suspected and that it could be pulling the most recently generated identity element from any table in the database from any session.  They also said that if we had a trigger which fired an insert that the identity from the nested insert could null the identity we were trying to insert into @ID.  So you were on the right track.

The answer they gave us was to try scope_identity() instead of @@identity.  We tried that and have been successful in getting the code to run.  Thanks for your input.  I am awarding all the points to you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question