Primeras versiones de los SP

El equipo ha empezado hacer los Stored Procedure de migración de datos, el equipo empezó a crear el xml para adelantar.
Documento XML version 1: Ver archivo xml

Stored Procedure de migración datos:

CREATE PROCEDURE [dbo].[ARSP_MigrateData]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
EXEC dbo.ARSP_ClearMovementDebug

IF(EXISTS(SELECT 1 FROM dbo.Debtor))
DELETE FROM dbo.Debtor;
IF(EXISTS(SELECT 1 FROM dbo.Loan))
DELETE FROM dbo.Loan;
IF(EXISTS(SELECT 1 FROM dbo.LoanType))
DELETE FROM dbo.LoanType;
IF(EXISTS(SELECT 1 FROM dbo.BillStatus))
DELETE FROM dbo.BillStatus;

DECLARE @Loan TABLE(ID INT,OriginalAmount MONEY,Fee MONEY,RemainingTerm INT,NABalance MONEY,ABalance MONEY,MonthAcumInterest FLOAT,Deadline INT,fkLoanType INT,fkDebtor int)
DECLARE @LoanType TABLE(ID INT,Name VARCHAR(50),InterestRate FLOAT,TermMonths INT)
DECLARE @Debtor TABLE(ID INT,Name VARCHAR(50))
DECLARE @BillStatus TABLE(ID INT,Name VARCHAR(50))

DECLARE @XML XML
SELECT @XML = BulkColumn
FROM OPENROWSET(BULK 'D:\XMLBasesAR.xml', SINGLE_CLOB) AS x;

INSERT INTO @BillStatus(ID,Name)
SELECT    estado.value('@ID', 'VARCHAR(50)'),
estado.value('@Name', 'VARCHAR(50)')
FROM @XML.nodes('/AccountReceivable') AS x1(AR)
cross apply x1.AR.nodes('./BillStatus') AS x2(estado)

INSERT INTO @LoanType(ID,Name,InterestRate,TermMonths)
SELECT tipo.value('@ID','VARCHAR(50)'),
tipo.value('@Name','VARCHAR(50)'),
tipo.value('@InterestRate','VARCHAR(50)'),
tipo.value('@TermMonths','VARCHAR(50)')
FROM @XML.nodes('/AccountReceivable') AS x1(AR)
cross apply x1.AR.nodes('./LoanType') AS x2(tipo)

INSERT INTO @Loan(ID,OriginalAmount,Fee,RemainingTerm,NABalance,ABalance,MonthAcumInterest,Deadline,fkLoanType,fkDebtor)
SELECT       prestamo.value('@ID', 'VARCHAR(50)'),
prestamo.value('@OriginalAmount', 'VARCHAR(50)'),
prestamo.value('@Fee', 'VARCHAR(50)'),
prestamo.value('@RemainingTerm', 'VARCHAR(50)'),
prestamo.value('@NABalance', 'VARCHAR(50)'),
prestamo.value('@ABalance', 'VARCHAR(50)'),
prestamo.value('@MonthAcumInterest', 'VARCHAR(50)'),
prestamo.value('@Deadline', 'VARCHAR(50)'),
prestamo.value('@LoanType', 'VARCHAR(50)'),
prestamo.value('@Debtor', 'VARCHAR(50)')
FROM @XML.nodes('/AccountReceivable') AS x1(AR)
cross apply x1.AR.nodes('./Loan') AS x2(prestamo)

INSERT INTO @Debtor(ID,Name)
SELECT    
deudor.value('@ID', 'VARCHAR(50)'),
deudor.value('@Name','VARCHAR(50)')
FROM @XML.nodes('/AccountReceivable') AS x1(AR)
cross apply x1.AR.nodes('./Debtor') AS x2(deudor)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
INSERT INTO dbo.BillStatus(ID,Name)
SELECT BS.ID, BS.Name
FROM @BillStatus BS

INSERT INTO dbo.LoanType(ID,Name,InterestRate,TermMonths)
SELECT LT.ID, LT.Name, LT.InterestRate, LT.TermMonths
FROM @LoanType LT

INSERT INTO dbo.Loan(ID,OriginalAmount,Fee,RemainingTerm,NonAppliedBalance,AppliedBalance,MonthAcumInterest,Deadline,FK_LoanType,FK_Debtor)
SELECT L.ID,L.OriginalAmount,L.Fee,L.RemainingTerm,L.NABalance,L.ABalance,L.MonthAcumInterest,L.Deadline,L.fkLoanType,L.fkDebtor
FROM @Loan L

INSERT INTO dbo.Debtor(ID,Name)
SELECT D.ID,D.Name
FROM @Debtor D
COMMIT

RETURN 1
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
PRINT 'Something went wrong!'
RETURN -1 * @@ERROR
END CATCH
END

GO

Stored Procedure para borrar todos los datos de las tablas:

CREATE PROCEDURE [dbo].[ARSP_ClearMovementDebug]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DELETE FROM dbo.AppliedBalanceMovement
DELETE FROM dbo.NonAppliedBalanceMovement
DELETE FROM dbo.DailyInterestMovement

END
GO