我目前正在进行转换，我需要将来自 Cobol 系统的数据转换为 SQL Server。我们使用 SSIS 将数据提取分析为临时表。然后，我们使用存储过程来构建可以验证并导入新系统的文件。我目前停留在如何处理以下示例数据的要求上。
本文中的示例数据提供了在我们的新系统中生成账单历史记录所需的数据。字段R_PS28_STRT_DT_1和R_PS28_STRT_DT_2指示该附表中第一张帐单的日期。而且，R_PS28_NO_PMT_1 和 R_PS28_NO_PMT_2 指示要插入给定贷款的账单历史记录表中的账单历史记录行数。
例如，根据提供的示例数据，第一行将生成 24 个INSERT报表，其帐单日期如下所示。我知道我可以打开一个游标并使用存储过程内部的循环处理每个计划。但是，这让我担心性能，因为生产表会很大。在游标和循环之外有更好的方法来解决这个问题吗？
5/29/2015
6/29/2015
7/29/2015
8/29/2015
9/29/2015
10/29/2015
11/29/2015
12/29/2015
1/29/2016
2/29/2016
3/29/2016
4/29/2016
5/29/2016
6/29/2016
7/29/2016
8/29/2016
9/29/2016
10/29/2016
11/29/2016
12/29/2016
1/29/2017
2/29/2017
3/29/2017
4/29/2017
R_KEY R_DUE_DTE_P R_PAST_DTE R_PAYMENT R_PS28_STRT_DT_1 R_PS28_NO_PMT_1 R_PS28_AMT_1 R_PS28_STRT_DT_2 R_PS28_NO_PMT_2 R_PS28_AMT_2
47200161755171001 2016-10-29 2016-05-29 349.94 2016-05-29 12 349.94 2015-05-29 12 0.00
47200161755251001 2016-10-20 2016-05-20 166.92 2016-05-20 12 166.92 2015-05-20 12 0.00
47200161755331001 2016-10-21 2016-05-21 337.78 2016-05-21 12 337.78 2015-05-21 12 0.00
47200161755411001 2016-11-04 2016-06-04 194.66 2016-06-04 12 194.66 2015-06-04 12 0.00
47200161755581001 2016-10-21 2016-05-21 225.99 2016-05-21 12 225.99 2015-05-21 12 0.00
下面是我实现存储过程以解决上述问题的第一遍。 它确实有效，但是，我不确定它在大型数据集上的表现如何，或者它是否是最佳解决方案。 我愿意接受任何建议。 我主要只是在寻找解决问题的更好方法的设计想法。 我知道我可以编写另一个过程来摆脱多余的 WHILE 循环。 我只是还没有解决这个问题。
/* Table-Value Function that generates Billing History rows. There are up
* to 8 schedules that require billing history to be generated.
*/
CREATE FUNCTION [dbo].[BuildBillingHistoryForLoan](@startDate1 datetime,
@numPayments1 int,
@pmtAmount1 money,
@freq1 int,
@startDate2 datetime,
@numPayments2 int,
@pmtAmount2 money,
@freq2 int,
@startDate3 datetime,
@numPayments3 int,
@pmtAmount3 money,
@freq3 int,
@startDate4 datetime,
@numPayments4 int,
@pmtAmount4 money,
@freq4 int,
@startDate5 datetime,
@numPayments5 int,
@pmtAmount5 money,
@freq5 int,
@startDate6 datetime,
@numPayments6 int,
@pmtAmount6 money,
@freq6 int,
@startDate7 datetime,
@numPayments7 int,
@pmtAmount7 money,
@freq7 int,
@startDate8 datetime,
@numPayments8 int,
@pmtAmount8 money,
@freq8 int,
@pastDueDate datetime,
@loanNumber varchar(30))
RETURNS @billingHistory TABLE (
LoanNumber varchar(30),
DueDate datetime,
PaymentAmount money
)
AS
BEGIN
DECLARE @counter INT
DECLARE @currentBillDate datetime
-- Schedule 1
SET @currentBillDate = @startDate1
SET @counter = 0
WHILE @currentBillDate is not null and @currentBillDate <> '1753-01-01' and @currentBillDate < @pastDueDate and @counter < @numPayments1
BEGIN
INSERT INTO @billingHistory values (@loanNumber, @currentBillDate, @pmtAmount1)
SET @counter = @counter + 1
SET @currentBillDate = dbo.CalculateNextPayment(@currentBillDate, @freq1)
END
-- Schedule 2
SET @currentBillDate = @startDate2
SET @counter = 0
WHILE @currentBillDate is not null and @currentBillDate <> '1753-01-01' and @currentBillDate < @pastDueDate and @counter < @numPayments2
BEGIN
INSERT INTO @billingHistory values (@loanNumber,@currentBillDate, @pmtAmount2)
SET @counter = @counter + 1
SET @currentBillDate = dbo.CalculateNextPayment(@currentBillDate, @freq2)
END
-- Schedule 3
SET @currentBillDate = @startDate3
SET @counter = 0
WHILE @currentBillDate is not null and @currentBillDate <> '1753-01-01' and @currentBillDate < @pastDueDate and @counter < @numPayments3
BEGIN
INSERT INTO @billingHistory values (@loanNumber, @currentBillDate, @pmtAmount3)
SET @counter = @counter + 1
SET @currentBillDate = dbo.CalculateNextPayment(@currentBillDate, @freq3)
END
-- Schedule 4
SET @currentBillDate = @startDate4
SET @counter = 0
WHILE @currentBillDate is not null and @currentBillDate <> '1753-01-01' and @currentBillDate < @pastDueDate and @counter < @numPayments4
BEGIN
INSERT INTO @billingHistory values (@loanNumber, @currentBillDate, @pmtAmount4)
SET @counter = @counter + 1
SET @currentBillDate = dbo.CalculateNextPayment(@currentBillDate, @freq4)
END
-- Schedule 5
SET @currentBillDate = @startDate5
SET @counter = 0
WHILE @currentBillDate is not null and @currentBillDate <> '1753-01-01' and @currentBillDate < @pastDueDate and @counter < @numPayments5
BEGIN
INSERT INTO @billingHistory values (@loanNumber, @currentBillDate, @pmtAmount5)
SET @counter = @counter + 1
SET @currentBillDate = dbo.CalculateNextPayment(@currentBillDate, @freq5)
END
-- Schedule 6
SET @currentBillDate = @startDate6
SET @counter = 0
WHILE @currentBillDate is not null and @currentBillDate <> '1753-01-01' and @currentBillDate < @pastDueDate and @counter < @numPayments6
BEGIN
INSERT INTO @billingHistory values (@loanNumber, @currentBillDate, @pmtAmount6)
SET @counter = @counter + 1
SET @currentBillDate = dbo.CalculateNextPayment(@currentBillDate, @freq6)
END
-- Schedule 7
SET @currentBillDate = @startDate7
SET @counter = 0
WHILE @currentBillDate is not null and @currentBillDate <> '1753-01-01' and @currentBillDate < @pastDueDate and @counter < @numPayments7
BEGIN
INSERT INTO @billingHistory values (@loanNumber, @currentBillDate, @pmtAmount7)
SET @counter = @counter + 1
SET @currentBillDate = dbo.CalculateNextPayment(@currentBillDate, @freq7)
END
-- Schedule 8
SET @currentBillDate = @startDate8
SET @counter = 0
WHILE @currentBillDate is not null and @currentBillDate <> '1753-01-01' and @currentBillDate < @pastDueDate and @counter < @numPayments8
BEGIN
INSERT INTO @billingHistory values (@loanNumber, @currentBillDate, @pmtAmount8)
SET @counter = @counter + 1
SET @currentBillDate = dbo.CalculateNextPayment(@currentBillDate, @freq8)
END
RETURN;
END;
/* INSERT Statement that tests the table-valued function to ensure that the
* correct number of rows are inserted for each row in the loan table.
*/
INSERT INTO temp_bh
select LoanNumber, DueDate, PaymentAmount
FROM RFELOAN1 Loan1
INNER JOIN RFELOAN3 Loan3 on Loan3.r_key = Loan1.r_key
CROSS APPLY dbo.BuildBillingHistoryForLoan(Loan3.R_PS28_STRT_DT_1,Loan3.R_PS28_NO_PMT_1,Loan3.R_PS28_AMT_1,Loan3.R_PS28_SCHED_1, -- Schedule 1
Loan3.R_PS28_STRT_DT_2,Loan3.R_PS28_NO_PMT_2,Loan3.R_PS28_AMT_2,Loan3.R_PS28_SCHED_2, -- Schedule 2
Loan3.R_PS28_STRT_DT_3,Loan3.R_PS28_NO_PMT_3,Loan3.R_PS28_AMT_3,Loan3.R_PS28_SCHED_3, -- Schedule 3
Loan3.R_PS28_STRT_DT_4,Loan3.R_PS28_NO_PMT_4,Loan3.R_PS28_AMT_4,Loan3.R_PS28_SCHED_4, -- Schedule 4
Loan3.R_PS28_STRT_DT_5,Loan3.R_PS28_NO_PMT_5,Loan3.R_PS28_AMT_5,Loan3.R_PS28_SCHED_5, -- Schedule 5
Loan3.R_PS28_STRT_DT_6,Loan3.R_PS28_NO_PMT_6,Loan3.R_PS28_AMT_6,Loan3.R_PS28_SCHED_6, -- Schedule 6
Loan3.R_PS28_STRT_DT_7,Loan3.R_PS28_NO_PMT_7,Loan3.R_PS28_AMT_7,Loan3.R_PS28_SCHED_7, -- Schedule 7
Loan3.R_PS28_STRT_DT_8,Loan3.R_PS28_NO_PMT_8,Loan3.R_PS28_AMT_8,Loan3.R_PS28_SCHED_8, -- Schedule 8
Loan1.R_PAST_DTE, Loan3.R_KEY) -- Past Due Date
WHERE Loan1.R_KEY = Loan3.R_KEY AND R_CLOS = 0 AND
((R_TYPE_X = '3' AND R_MODE_X = '3') OR (R_TYPE_X = '6' AND R_MODE_X = '2'))
-- R_TYPE_X (3 = Simple Int Amortization, 6 = Rule of 78s Amortization)
-- R_MODE_X (3 = Principal and Interest with BH
您可以创建表值函数以返回每条记录的计费历史记录。
然后将此功能cross apply或outer apply导入的数据。结果将是您在 1select内导入的所有数据的完整帐单历史记录。
现在您可以使用单个insert语句插入数据
像这样的东西
INSERT INTO dbo.BillingHistory
SELECT ...
FROM sample_data_you_posted SD
CROSS APPLY dbo.fn_BillingHistory(SD.R_PS28_STRT_DT_1, SD.R_PS28_STRT_DT_2, SD.R_PS28_NO_PMT_1, SD.R_PS28_NO_PMT_2)