Ashwin_shastry
asked on
Delete and Insert in the same stored procedure
Hello,
It seems like a fairly simple problem but unable to figure out what the problem is as my brains are fried.
I have a stored procedure which will first delete all the rows and then insert rows into them.
The problem i am facing is that if there are rows already in the table, I get an exception from the application i am trying to run this stored procedure.
I have attached the exception and the sql query.
As a test i had a couple of rows in the table and I was trying to delete it and re-insert the same rows into the table. Since i am trying to re-insert the same rows I get primary key violation exception. Not sure where i am going wrong.
Hoping for some help.
AJ
SQLQuery59.sql
Exception.txt
It seems like a fairly simple problem but unable to figure out what the problem is as my brains are fried.
I have a stored procedure which will first delete all the rows and then insert rows into them.
The problem i am facing is that if there are rows already in the table, I get an exception from the application i am trying to run this stored procedure.
I have attached the exception and the sql query.
As a test i had a couple of rows in the table and I was trying to delete it and re-insert the same rows into the table. Since i am trying to re-insert the same rows I get primary key violation exception. Not sure where i am going wrong.
Hoping for some help.
AJ
SQLQuery59.sql
Exception.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No, as I mentioned, the data in the table contains the records that are duplicated by themselves. Not against the existing data. As I said show us what you are inserting
ASKER
Hello,
Sorry for the delay in replying back. I was out sick for a few days.
Here is an image (attached) of two records which are currently in the table. Now the table which holds these values has no longer any primary key. So the error I was getting earlier is not an issue any more.
BUT, as the business rules dictates, every time I insert records into this table, I will first need to delete the records and then insert.
Now if I were to insert 4 records into the table, 2 of which are the same as the ones already existing as shown in the attachment, I should Ideally be able to delete the 2 records already existing in the table and insert the 4 records.
But for what ever reason I see 6 records, the two old records are not deleted so I end up with duplicates.
So all I want to do is this simple task in one SINGLE stored procedure,
Delete all the records in the table.
Insert all the records passed into the stored procedure.
Hoping for some help.
AJ
UnregisteredLoyaltyCards.JPG
Sorry for the delay in replying back. I was out sick for a few days.
Here is an image (attached) of two records which are currently in the table. Now the table which holds these values has no longer any primary key. So the error I was getting earlier is not an issue any more.
BUT, as the business rules dictates, every time I insert records into this table, I will first need to delete the records and then insert.
Now if I were to insert 4 records into the table, 2 of which are the same as the ones already existing as shown in the attachment, I should Ideally be able to delete the 2 records already existing in the table and insert the 4 records.
But for what ever reason I see 6 records, the two old records are not deleted so I end up with duplicates.
So all I want to do is this simple task in one SINGLE stored procedure,
Delete all the records in the table.
Insert all the records passed into the stored procedure.
Hoping for some help.
AJ
UnregisteredLoyaltyCards.JPG
Can you clarify how are you able to see these 6 records?
Can you show is the 4 records you are trying to insert. As I mentioned two times already these 4 records might be a problem
Can you show is the 4 records you are trying to insert. As I mentioned two times already these 4 records might be a problem
ASKER
Thanks for the reply. Yes the business logic is such that the table param might contain data which already exists in the table i am trying to insert. Hence I am trying to delete all the data in the table before inserting.
I am guessing the error is because I am not doing a commit after delete, is that the case ?
Thanks,
AJ