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
Ashwin_shastryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Very easy. Your input data passed by @TableParam contains duplicate entries. Can you show us what you are trying to insert
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ashwin_shastryAuthor Commented:
Hello,

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
0
chaauCommented:
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
0
Ashwin_shastryAuthor Commented:
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
0
chaauCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.