Stored procedure that reinserts existing data

Hello

Here is the 'Donations' table with sample data:

Bank_Account | Amount | Reference | Payment_Method | Rate      |  Date      |
B33                  |      10    |  12345       |         CC                |   DWM   | 21/07/07 |

I need a stored procedure that will check to see if a matching Reference number already exists and if it does to reinsert the Bank_Account, Payment_Method, Refrence and Rate details into the same Donations table. The 'Amount' figure will be passed into it from the application, and I need today's date for the Date column (using GetDate())

All replies appreciated!

Thanks.



SigmundFraudAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
This solution will guarantee insertion of one record only:
CREATE PROCEDURE MyStoredProcedure (@RefNumber INT, @Amount INT)
  
  INSERT INTO Donations (Bank_Account, Amount, Reference, Payment_Method, Rate, Date)
  SELECT TOP 1 Bank_Account, @Amount, Reference, Payment_Method, Rate, GETDATE()
  FROM Donations 
  WHERE Reference = @RefNumber 
 
GO

Open in new window

0
 
Jinesh KamdarCommented:
Use this SQL to see if there are any existing records for the reference no. in question.
@cnt = SELECT COUNT(1) FROM donations WHERE reference = p_ref

IF cnt > 0 THEN
    UPDATE ...
ELSE
    INSERT ...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If I understood you'll need something like:
CREATE PROCEDURE MyStoredProcedure (@RefNumber INT, @Amount INT)
  
  INSERT INTO Donations (Bank_Account, Amount, Reference, Payment_Method, Rate, Date)
  SELECT Bank_Account, @Amount, Reference, Payment_Method, Rate, GETDATE()
  FROM Donations 
  WHERE Reference = RefNumber 
 
GO

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Missed '@' at RefNumber. Should be:
CREATE PROCEDURE MyStoredProcedure (@RefNumber INT, @Amount INT)
  
  INSERT INTO Donations (Bank_Account, Amount, Reference, Payment_Method, Rate, Date)
  SELECT Bank_Account, @Amount, Reference, Payment_Method, Rate, GETDATE()
  FROM Donations 
  WHERE Reference = @RefNumber 
 
GO

Open in new window

0
 
SigmundFraudAuthor Commented:
Thanks for the responses.
I will test the solutions in the morning.
0
 
Jinesh KamdarCommented:
>> if it does to reinsert the Bank_Account, Payment_Method, Refrence and Rate details into the same Donations table
By "re-insert" do u mean to update the columns of the existing row or insert a new record with the same refnumber even if a record with that number already exists?
0
 
SigmundFraudAuthor Commented:
The second one: "insert a new record with the same refnumber even if a record with that number already exists?"
0
 
Jinesh KamdarCommented:
1. And what if no record with that number exists?
2. What if multiple records for that no. exist with diff. details in the other fields? Which one wud u want to re-insert?
0
 
SigmundFraudAuthor Commented:
jinesh:
1. And what if no record with that number exists?
Then nothing should happen.

2. What if multiple records for that no. exist with diff. details in the other fields? Which one wud u want to re-insert?
Allt he fields will be the same except for what is supplied by the application.

VMontalvao:
Thanks for the solution, it is definitely what i am looking for, however it is not inserted any rows - any ideas?




0
 
Jinesh KamdarCommented:
@ SigmundFraud
>> Allt he fields will be the same except for what is supplied by the application.
They will same as what? Say u already hv 2 records existing for ref_no = 123, one with payment_method as CC and the other with payment_method as CASH. Out of these 2, which record vil u select for re-insert and on what criteria?
0
 
SigmundFraudAuthor Commented:
jinesh, that situation should never occur.

If you have 2 records for ref_no 123, the 2nd the payment_method will be copied from 1st and so they will match - this is the case for many of the columns.

The only columns that will vary from what is already in the table will be the Amount (passed into it from the application.) and the date.

Hope that helps.
0
 
Jinesh KamdarCommented:
In that case, VMontalvao's solution should have worked. Just fire the SELECT on the console and see if u fetch any records for that ref_no, might be there are no existing records for the ref_no that u passed.
0
 
SigmundFraudAuthor Commented:
Works great, thanks.
0
 
SigmundFraudAuthor Commented:
Yep it works now, i just had to make some modifications to the base table. Thanks guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.