Solved

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

Posted on 2009-04-07
7
586 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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

821 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