Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2003-11-08
11
Medium Priority
?
1,530 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.

876 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