Need help with simple delete and insert with the input parmeter

Question

Post reply

Need help with simple delete and insert with the input parmeter

Best-330874

SSCrazy

Points: 2350
More actions
November 8, 2006 at 11:12 am

#76607

Hi,
   i have a stored procedure which takes an input parameter "month_of_file" .i want that every time i execute it it should delete the old record and insert the new record if it is the same in the table.if the record is not the same it should do the insert in the table.
code
CREATE PROCEDURE GE_Transaction_Record_Counts
       @Month_of_file_filter datetime
AS
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TransactionCounts]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TransactionCounts]
CREATE TABLE [dbo].[TransactionCounts] (
       [Month_of_file] [datetime] NULL ,
       [CalcAction] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
       [TableValue] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
       [CalculatedValue] [int] NULL
) ON [PRIMARY]
INSERT INTO TransactionCounts (Month_of_file,
CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter as Month_of_file, 'Total Record Count' as CalcAction, SPACE(1) as TableValue,
COUNT(*) as CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
INSERT INTO TransactionCounts (Month_of_file,
CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter as Month_of_file,
'Record Count by Transaction Month' as CalcAction,
MonthReported as TableValue, COUNT(*) as
CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
       GROUP BY MonthReported
INSERT INTO TransactionCounts (Month_of_file,
CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter, 'Record Count By Transaction Type' as CalcAction, TransactionType as
TableValue, COUNT(*) as CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
       GROUP BY TransactionType
INSERT INTO TransactionCounts (Month_of_file,
CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter as Month_of_file,
'Record Count by Status' as CalcAction, Status as
TableValue, COUNT(*) as CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
       GROUP BY Status
INSERT INTO TransactionCounts (Month_of_file,
CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter as Month_of_file,
'Record Count of Unique Policies' as CalcAction,
SPACE(1) as TableValue, COUNT(DISTINCT PolicyNumber)
as CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
INSERT INTO TransactionCounts (Month_of_file,
CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter as Month_of_file,
'Premium Total' as CalcAction, SPACE(1) as TableValue,
SUM(Amount) as CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
INSERT INTO TransactionCounts
(Month_of_file,CalcAction, TableValue,
CalculatedValue)
       SELECT @Month_of_file_Filter as
Month_of_file,'Negative Premium Totals' as CalcAction,
SPACE(1) as TableValue, SUM(Amount) as CalculatedValue
       FROM GE_Transaction
       WHERE Amount < 0 AND Month_of_file =
@Month_of_file_filter
INSERT INTO TransactionCounts (Month_of_file,
CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_File_filter as Month_of_file,
'Premium by Transaction Type' as CalcAction,
TransactionType as TableValue, SUM(Amount) as
CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
       GROUP BY TransactionType
INSERT INTO TransactionCounts (Month_of_file,
CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_File_Filter as Month_of_File,
'WOP by Transaction Type' as CalcAction,
TransactionType as TableValue,
SUM(PremiumWaivedAmount) as CalculatedValue
       FROM GE_Transaction
       WHERE Month_of_file = @Month_of_file_filter
       GROUP BY TransactionType
GO

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

SQLprog SSC Enthusiast Points: 186 More actions · Answer 1

You can add these queries before the first insert into just an example for the first part which is TotalCount)

select @Month_of_file_filter = COUNT(*)

FROM GE_Transaction

WHERE Month_of_file = @Month_of_file_filter

if

exists ( select 1 from TransactionCounts

where CalcAction = 'Total Record Count'

and TableValue = ' '

and CalculatedValue = @Month_of_file_filter )

delete

TransactionCounts where CalcAction = 'Total Record Count'

and TableValue = ' '

and CalculatedValue = @Month_of_file_filter