December 15, 2011 at 1:33 pm
Hello All,
I have stored procedure like this
USE [rlk_test]
GO
/****** Object: StoredProcedure [dbo].[usp_Dashboard] Script Date: 12/15/2011 09:21:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Dashboard_test]
AS
/*Cut_Date*/
declare @cut_date date
set @cut_date = DATEADD(d,-1,getdate())
/*Invoice Generated*/
declare @invoice_gen decimal(12,2)
set @invoice_gen = (
select SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) line_tot
from billing_payment
where TYPE_DESC != 'Level Pay Contract'
and type_desc != 'Transfer to Payment Contract'
and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Regulated',
'Cash - Revenue - Non Regulated',
'Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Non Regulated',
'Deposit Charge - Non Regulated'))
/*Payments Received*/
declare @payment_received decimal(12,2)
set @payment_received = (
select round(SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT),2) 'Payments Recieved'
from billing_payment
where TYPE_DESC = 'Payment')
/*Unclaimed Payments*/
declare @unclaimed_payment decimal(12,2)
set @unclaimed_payment = (
select SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 'unclaimed Payments'
from billing_payment
where (customer_tkn = 42372942
and CUST_STATEMENT_TKN is null
and CURRENT_STMT_DATE is null))
/*Payments_received --- Collections Phase*/
Declare @coll_payment_received decimal(12,2)
set @coll_payment_received= cast((@payment_received - @unclaimed_payment) * -1 as varchar(14));
/*Tdu charges*/
declare @tdu_charge decimal(12,2)
set @tdu_charge = (
select round(SUM(CALC_PRICE_AMT + price_tax_amt),2) 'tdu_charge'
from billing_payment
where TYPE_desc = 'Regular Bill')
Insert into dbo.test
select @cut_date,@invoice_gen,@payments_received,@unclaimed_payments,@coll_payments_received,@tdu_charges
If I execute this procedure and query the table I will get the output as
2011-12-14 500 600 700 800 900
Suppose if I have some change in one of the calculations(500 ---> 1200) for the same date and If i execute the store procedure again it should give me the result as
2011-12-14 1200 600 700 800 900
So it should overwrite the existing the data.It should not add an other row like this
2011-12-14 500 600 700 800 900
2011-12-14 1200 600 700 800 900
How to achieve this.
Thanks for any help.
December 15, 2011 at 1:48 pm
What you're asking about is what's called an "upsert". In other words "update if existing, insert if not".
In SQL 2008, you can use the "Merge" command to do this. Take a look at that one in MSDN.com.
If you're using something prior to 2008, the usual way to do it is to issue and Update command, and then check @@rowcount. If it didn't update anything, you then issue an Insert command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 2:00 pm
But the problem here is I dont have an update statement. All the calculations are coming from the source data. If the source data changes then some of the numbers in sp will change. I am not updating anything in sp.Its all depends on source data. If I execute the stored procedure today it will insert all the calculations for today's data. If there is some change in todays data then All my numbers inside my sp will change and if I execute sp then it will insert and other row with the different data with same date.I dont want that to happen. It should just replace the numbers and keep the date as it is...
Do I still need to use merge?
December 15, 2011 at 2:03 pm
That's exactly what Merge is for.
You re-write the stored procedure to use Merge instead of Insert, test it, and it should do exactly what you need.
Just make sure to do so in a dev environment, of course. All the usual procedures on coding apply.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 2:50 pm
Thanks for your time. I will try that approach
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply