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

• SQLprog

  SSC Enthusiast

  Points: 186

  More actions

  November 9, 2006 at 7:29 am

  #670835

  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