Solved

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

Posted on 2003-11-08
11
1,348 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
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
Comment Utility
So what was the outcome to namasi_navaretnam question?  Were there 9 columns after all?

Anthony
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now