Solved

Identical Tables "Insert Error: Column name or number of supplied values does not match table definition"

Posted on 2009-04-07
7
595 Views
Last Modified: 2013-11-30
I wrote a stored procedure to dynamically build the insert statement as I am using it as part of an SSIS package.  It only fails on certain tables, not all of the tables. I am testing right now and the frustrating part is that this worked yesterday.  I copied the table from dev to my test environment and I am comparing test with dev.  Obivously, they match since I copied the table from dev to test.  I have been debugging through the code and the dev table, the test table and the staging table all match.  They all have the same columns in the same order with the same datatypes.  I am getting this error when I execute the stored procedure from the package.  When I run the stored procedure in the Management Studio, I don't get the error.  Any ideas???
0
Comment
Question by:seasonmmclane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Expert Comment

by:Auric1983
ID: 24089144
Are you trying to insert into an identity field?
0
 

Author Comment

by:seasonmmclane
ID: 24089809
No.  A big part of the mystery to me is that if I run the script in SSMS, it works.  Another thing...The package will execute successfully for most tables.  it is just a few tables that are giving me problems.  I don't have any databases or tablenames hardcoded.
0
 

Author Comment

by:seasonmmclane
ID: 24091665
I figured out one problem.  When I try to run this in DEV against QA (comparison), I am trying to insert into an identity field.  Any ideas???
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 24104368
Well Auric1983 picked the identity business...

Depending on how you wrote the dynamic part, what you can do is check or use sys.columns which has the is_idenity attribute and exclude that from the insert. Found that system / catalogue view to be one of the handiest.

The other thing to do is to wrap it up in a try/catch block - but whilst it might catch the error, it doesn't fix the problem.

Updating with identities is always a challenge, like, do you need to preserve the value (as in a new master record with details already point to it). In which case you might need to allow insert into the odentity, but if there are multiple streams of data into that table then it is likely to cause problems.

Can you show us the procedure ? Might be able to make some recommendations.





0
 
LVL 16

Assisted Solution

by:Auric1983
Auric1983 earned 250 total points
ID: 24106726
You can specify that you want to insert into an identity column.  

SET IDENTITY_INSERT tablename ON
Insert records
SET IDENTITY_INSERT tablename OFF
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24108852
Please do not do that without careful consideration of number sequences and potentially different data sources. If all coming from the other table, then fine, but if there is anything that can add to that table being the target of the insert then you are going to have problems. That identity insert setting is really only for initial data takeup.
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24129354
If creating a dev environment from Test / Production is what you are after I'd do a full backup / restore, the only problem there is you lose any specific security applied only to the dev db.

But Mark is definately right that using the identity insert you really need to understand what data is being inserted...
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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