Stored procedure help

Question

Post reply

Stored procedure help

Best-330874

SSCrazy

Points: 2350
More actions
November 20, 2006 at 2:11 pm

#121579

Hi,
   i have a stored procedure which creates a table TransactionCounts as follows and does the inserts as follows
--------------------------------------------------------------------------------------------
CREATE PROCEDURE GE_Transaction_Record_Counts
       @Month_of_file_filter datetime
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TransactionCounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[TransactionCounts] (
       [ID] [int] IDENTITY (1, 1) NOT NULL ,
       [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]
begin
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 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, '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


END
GO
--------------------------------------------------------------------------
TransactionCounts_Sort

CREATE TABLE [TransactionCounts_Sort] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CalcAction] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SortOrder] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_TransactionCounts_Sort] PRIMARY KEY CLUSTERED
(
  [ID]
&nbsp ON [PRIMARY]
) ON [PRIMARY]
GO
--------------------------------------------------------------------------------
Values in the table TransactionCounts_Sort

   ID     CalcAction                                          SortOrder
1          Total Record Count                                    1
2          Record Count of Unique Policies                     2
3           Record Count by Status                            3

what i want to do is replace the values of CalcAction Column in TransactionCounts table with the values of CalcAction Column
in the second table which is TransactionCounts_Sort

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You want to update instead of inserting, you you need to update data between 2 different tables?

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

What do you mean i dont understand i guess what are you saying.Can you be a little more clear please.

THANKS

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Please post sample data from all tables and the required output you need us to create. SQL and data are universal language .

Best-330874 SSCrazy Points: 2350 More actions · Answer 4

Values in the table TransactionCounts_Sort-sampledata

ID CalcAction SortOrder

1 Total Record Count 1

2 Record Count of Unique Policies 2

3 Record Count by Status 3

Value in TransactionCounts-sample data

ID Month_of_file CalcAction TableValue CalculatedValue

1 12/1/2004 Total Record Count 66476

2 12/1/2004 Record Count of Unique Policies 30356

3 12/1/2004 Record Count by Status A 66293

4 12/1/2004 Record Count by Status D 183

Best-330874 SSCrazy Points: 2350 More actions · Answer 5

what i want to do is replace the values of CalcAction Column in TransactionCounts table with the values of CalcAction Column

in the second table which is TransactionCounts_Sort

SQLBill SSC Guru Points: 51440 More actions · Answer 6

From the examples you give, and the question...I don't see what you are trying to do. You say you want to remove the values in the CalcAction column in the bottom table with the exact same values from the top table. That doesn't sound right.

So, based on the two tables you showed us, what do you want the final result to look like?

-SQLBill

Best-330874 SSCrazy Points: 2350 More actions · Answer 7

Value in TransactionCounts- what iam looking for

ID Month_of_file CalcAction TableValue CalculatedValue

1 12/1/2004 1 66476

2 12/1/2004 2 30356

3 12/1/2004 3 A 66293

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Is the 'new' CalcAction based on the TransactionCounts_Sort.SortOrder or the TransactionCounts_Sort.ID?

What do you want to have happen with the second Record Count by Status row?

Something like:

UPDATE TransactionCounts

SET TransactionCounts.CalcAction = TransactionCounts_Sort.CalcAction

FROM TransactionCounts, TransactionCounts_Sort

WHERE TransactionCounts.ID = TransactionCounts_Sort.ID

-SQLBill

Best-330874 SSCrazy Points: 2350 More actions · Answer 9

it will show 3 in CalcAction column of transactioncount table.Dont you think i have to establish some sort of relationship between two tables such as primary key and foreign key

SQLBill SSC Guru Points: 51440 More actions · Answer 10

No. Not for a basic update like that. Primary/Foreign keys are for when you are inserting data and you need to maintain integrity of the data. You just said you want to update some data in one table.

-SQLBill