Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored procedure that reinserts existing data

Posted on 2007-11-29
14
Medium Priority
?
198 Views
Last Modified: 2010-04-21
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.



0
Comment
Question by:SigmundFraud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20374886
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 20376059
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 20376064
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 800 total points
ID: 20376089
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
 

Author Comment

by:SigmundFraud
ID: 20376347
Thanks for the responses.
I will test the solutions in the morning.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20376639
>> 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
 

Author Comment

by:SigmundFraud
ID: 20376890
The second one: "insert a new record with the same refnumber even if a record with that number already exists?"
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20379934
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
 

Author Comment

by:SigmundFraud
ID: 20381332
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20381401
@ 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
 

Author Comment

by:SigmundFraud
ID: 20381449
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20381869
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
 

Author Closing Comment

by:SigmundFraud
ID: 31411705
Works great, thanks.
0
 

Author Comment

by:SigmundFraud
ID: 20382058
Yep it works now, i just had to make some modifications to the base table. Thanks guys!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

715 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