Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-04-07
7
Medium Priority
?
647 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

578 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