Overview
I created this stored procedure process billing data by converting multiple amount columns from a source table into individual rows in a billing amounts table. It accepts the name of a source table as a parameter and dynamically reads all columns that begin with the word ‘Amount’.
The name of the source billing table that contains the account number and multiple amount columns.
Process Description
1. Procedure Start
The procedure begins by printing a message indicating that the process of building billing amounts has started.
ALTER PROCEDURE [dbo].[CreateBillDataAmounts]
@SQLTable varchar(100)
AS
BEGIN
print 'Start Building Amounts'
drop table if EXISTS #tmpTable
DECLARE @MyCursor CURSOR;
create table #tmpTable (AccountNumber varchar(20), AmtTypeDesc varchar(20), Amount decimal(15,2))
DECLARE @SQL VARCHAR(MAX);
DECLARE @MyField VARCHAR(100);
declare @DBName varchar(100);
set @DBName = (SELECT distinct TABLE_CATALOG
FROM INFORMATION_SCHEMA.COLUMNS c)
BEGIN
TRUNCATE TABLE [BillDataAmounts_Raw]
SET @MyCursor = CURSOR FOR
SELECT COLUMN_NAME as AmtTypeDesc
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE
c.TABLE_NAME = @SQLTable AND
upper(c.COLUMN_NAME) like 'AMOUNT%'
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @MyField
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'insert into BillDataAmounts_Raw (AcctNumIDFK, AmtTypeDesc, Amount)
select AccountNumber,'''+ @MyField +''' as AmtTypeDesc, ' + @MyField + ' as Amount
from ' + @SQLTable
print @SQL
exec sp_sqlexec @SQL
FETCH NEXT FROM @MyCursor
INTO @MyField
END;
exec spTruncateBillDataAmounts
INSERT INTO [BillDataAmounts]
([AcctNumIDFK]
,[AmtTypeIDFK]
,[Amount]
,[CreateDateTime]
)
(
select t.AcctNumIDFK,aty.AmountTypeID,
ISNULL(t.Amount,0.00), getDate() from [BillDataAmounts_raw] t
inner join [BillDataAmountTypes] aty
on t.AmtTypeDesc=aty.AmtTypeDesc
)
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;
2. Temporary Table Setup
A temporary table named #tmpTable is created with the following fields:
– AccountNumber
– AmtTypeDesc
– Amount
3. Clearing Staging Data
The table BillDataAmounts_Raw is truncated to remove any existing records before new data is inserted.
4. Identifying Amount Columns
The procedure queries INFORMATION_SCHEMA.COLUMNS to find all columns in the specified table whose names start with ‘Amount’. Each column name represents a different billing amount type.
5. Cursor Processing
A cursor loops through each of the identified amount columns. For every column found, dynamic SQL is constructed that selects:
– AccountNumber
– The column name as AmtTypeDesc
– The value of the amount column as Amount
This data is inserted into the BillDataAmounts_Raw staging table.
6. Preparing the Final Table
After all amount columns have been processed and inserted into the raw table, another procedure named spTruncateBillDataAmounts is executed to clear the final destination table.
7. Inserting into the Final Table
Data is inserted from BillDataAmounts_Raw into BillDataAmounts. During this step:
– The account number is stored as AcctNumIDFK
– The amount type description is matched with BillDataAmountTypes to retrieve the corresponding AmountTypeID
– The amount value is stored
– The current date and time are recorded as CreateDateTime
8. Cleaning Account Numbers
After insertion, two update statements remove dash (-) and period (.) characters from the account number field AcctNumIDFK in the BillDataAmounts table.
Result
The procedure transforms a table containing multiple billing amount columns into a normalized row-based structure where each account and amount type is stored as a separate record in the BillDataAmounts table.