Solved

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

Posted on 2003-11-08
11
1,365 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

911 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

16 Experts available now in Live!

Get 1:1 Help Now