March 20, 2017 at 8:24 am
I have 4 records in temp table ; and form temp I am trying to insert into main table; for testing purpose I want to fail third record when inserting into main table to see if its going to exception or not.
May I know the process for this?
March 20, 2017 at 8:39 am
You'll have to provide more information. How is the insert working, how do you test an exception? Is this through some process?
March 20, 2017 at 8:48 am
Generally INSERTs are done as a SET and the INSERT would succeed or fail as a whole.
If you just want to test your error handling, use a THROW or RAISERROR. That won't be tied to any particular row.
If you want to test a particular type of error (PK violation, arithmetic overflow, etc.), you need to set up your data so that it causes that particular error, but, again, the error will cause the entire INSERT to fail. (I don't recommend using a RBAR approach.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 20, 2017 at 12:21 pm
Yes, I want to reporoduce the same the same that is happening in our existing system.. The error is created as 547 means it s a violation constraint.
I just want to test my new logic to see if one record fails is remaining processing or its rollbacking all the records into error table.
This is the data make up on temp table - How to tweak this data for violation issue?
--(220,'A','John,'UP847587848',NULL,0,89.80,'BN',3),
--(220,'A',' Smith,'UP126315673',NULL,0,89.80,'BN',3),
--(220,'A',' Rosse,'UP895877587',NULL,0,89.80,'BN',3),
--(220,'A',' Stella,'UP485775887',NULL,0,'89.84','BN',3)
March 20, 2017 at 2:31 pm
First, there isn't an error table from an insert. Second, we can't see your data, so we have no idea what a violation would be. Rather than a little test data, how about some table DDL with DRI constraints? Help us to know what you are dealing with. Then, how do you execute this? There won't be any error rows captured from T-sqL. If you use SSIS or something else, maybe there will be.
March 21, 2017 at 9:45 am
A 547 error is a Foreign Key Violation. You're trying to insert a value in your table that references a record in another table, but the other table doesn't contain a record with that value. As Steve said, we can't see your data, so we can't tell you how to set up your data to force a Foreign Key Violation.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply