SQL query to generate multiple records from every record of a table

Question

I have a table with 3000 records, and using every single one of them, I have to generate around 200 records (600k records in total), and insert them in a second table through SQL Server 2012.

I tried doing this using VBA (Selecting data from first table, calculating, and then inserting in second table), but the insertion is too slow, and takes forever. Someone suggested that I do the calculations/insertion directly in SQL, and that's what I'm trying to do, but I'm not very familiar with the language.

Here's the VBA code basically :

SQLStr = "SELECT * FROM TABLE_PRETS"
Set rs = cn.Execute(SQLStr)

If Not (rs.EOF And rs.BOF) Then
  rs.MoveFirst
    Do Until rs.EOF = True
      loanID = rs!N_CONTRAT
      remainingBalance = rs!MONTANT_CREDIT
      interestRate = rs!TAUX_ACTUEL / 100
      insuranceRate = rs!TAUX_ASSURANCE
      taxRate = rs!TVA
      startDate = rs!DATE_DEBUT
      monthlyRate = (interestRate * (1 + taxRate) + insuranceRate) / 12

      For i = 1 To rs!DUREE
        startingBalance = Round(remainingBalance, 2)
        principal = Round(rs!MENSUALITE - startingBalance * monthlyRate, 2)
        interestPaymentBT = Round(startingBalance * interestRate / 12, 2)
        taxOnInterest = Round(interestPaymentBT * taxRate, 2)
        insurancePayment = Round(startingBalance * insuranceRate, 2)
        remainingBalance = Round(startingBalance - principal, 2)

        SQLStr = "INSERT INTO TABLE_AMORTISSEMENT (N_CONTRAT,MOIS,DATE_ECHEANCE,MENSUALITE,SOLDE_DEPART,CAPITAL_AMORTI," _
        & "INTERET_HT,TVA,ASSURANCE,CAPITAL_RESTANT)" _
        & "VALUES (" & loanID & ", " & i & ", '" & DateAdd("m", i, startDate) & "', " & Replace(rs!MENSUALITE, ",", ".") & ", " _
        & Replace(startingBalance, ",", ".") & ", " & Replace(principal, ",", ".") & ", " & Replace(interestPaymentBT, ",", ".") _
        & ", " & Replace(taxOnInterest, ",", ".") & ", " & Replace(insurancePayment, ",", ".") & ", " _
        & Replace(remainingBalance, ",", ".") & ");"
        Set rs2 = cn.Execute(SQLStr, , adExecuteNoRecords)
      Next i
      rs.MoveNext
    Loop
End if

Here's where I got so far in SQL, but I'm pretty stuck :

DECLARE contracts_cursor CURSOR FOR
  SELECT N_CONTRAT, DATE_DEBUT, DUREE, MONTANT_CREDIT, TAUX_ACTUEL / 100 AS TAUX_CREDIT, TAUX_ASSURANCE, TVA, (TAUX_ACTUEL / 100  * (1 + TVA) + TAUX_ASSURANCE) / 12 AS TAUX_MENSUEL
  FROM TABLE_PRETS

DECLARE @index INT, @startingBalance FLOAT, @principal FLOAT, @interestPaymentBT FLOAT, @taxOnInterest FLOAT, @insurancePayment FLOAT, @remainingBalance FLOAT
SET @index = 0;

OPEN contracts_cursor;
FETCH NEXT FROM contracts_cursor;

WHILE @@FETCH_STATUS = 0
  BEGIN
    FETCH NEXT FROM contracts_cursor;
    --My brain stops working here
  END;

CLOSE contracts_cursor;
DEALLOCATE contracts_cursor;
GO

The problem is, I don't know if SQL can calculate a whole column at once (like Matlab) or if I have to loop through values in a column.

Exemple of first table DATA :

Exemple of result from first table data (84 rows actually) :

Sample data and desired results would really help explain what you want to do. — Gordon Linoff, Aug 26 '16 at 08:20
Could it all be done in SQL, so 'insert into table2 (field1) values (table1.field1*10)' for example http://stackoverflow.com/questions/74162/how-to-do-insert-into-a-table-records-extracted-from-another-table — Nathan_Sav, Aug 26 '16 at 08:24
Looks like recursive CTE is needed due to round() in `principal = Round(rs!MENSUALITE - startingBalance * monthlyRate, 2) ... startingBalance = Round(startingBalance - principal, 2)` — Serg, Aug 26 '16 at 08:55
what strikes me is that the values you are calculating to go in table TABLE_AMORTISSEMENT are calculable from table TABLE_PRETS, therefore you might want to consider making a VB function that calculates what you are interested in on demand. For a loan period of just a few hundred months max, those calculations will not create much overhead for many situations. — Cato, Aug 26 '16 at 09:14
@AndrewDeighton You are right, but the calculations aren't the problem. Running the code without inserting into the second table takes no time. — Naucle, Aug 26 '16 at 09:21
The insurance rate in your example doesn't look quite right - on the first payment row, the rate 0.0036 x 300000 = 1080, not 90. So looks like I'm missing a division by 12 somewhere? — doza, Aug 26 '16 at 09:33
@doza good find, there have to be a /12 there (the rate is annual). The tables are actually calculated in Excel, not using this code, that's why the result is different. Thank you. — Naucle, Aug 26 '16 at 09:41
@Naucle - that's related to my point, DB operations are often a larger overhead than mathematical calculations. DBs are for storing items that cannot be calculated, such as customer information. By saving this calculable information in a database table, you are potentially doing more work to retrieve the data again then it would to work it out when you need it. On top of that, you are using up database resources. You might want to check if you can actually calculate the values more quickly than you can retrieve them — Cato, Aug 26 '16 at 09:43
of course you might be doing something else with the data, like joining it as part of a report - I'm not ordering you around here. Hey did you know you could do all those calculations in an SQL server function, and return them from a table function - is a possibility — Cato, Aug 26 '16 at 09:45
@AndrewDeighton Good point. The thing is, the data in the second table is going to be used frequently to test multiple scenarios (selecting specific maturities/clients/periods, grouping by months/semesters/quarters...) which can be done using simple SQL queries once I have that table. I can't afford to generate the same table every time I want to try a new scenario. — Naucle, Aug 26 '16 at 09:58
@Parfait DUREE is french for "term" or "length" or "duration", it's basically the difference between the end date and the start date of the loan in months => the number of payments. — Naucle, Aug 26 '16 at 13:09

Pரதீப் · Answer 1 · 2016-08-26T08:55:16.677

Not sure why you are doing this but no need to use CURSOR to do this. Try this SET Based Approach

First generate 200 records using the below stacked CTE. Using the first 10 records am cross joining them to generate dummy 200 records. Execute each CTE to know the result(how many records generated).

;WITH fst(n)
     AS (SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1),
     scd(n)
     AS (SELECT a.n
         FROM   fst a
                CROSS JOIN fst b),
     data(n)
     AS (SELECT a.n
         FROM   scd a
                CROSS JOIN (SELECT 1 a
                            UNION ALL
                            SELECT 1) b)
SELECT *
FROM  data --200 records

After generating 200 records of dummy(1) data cross join the original table to get a Cartesian product of CTE data and your original table (ie) 3000 * 200 = 600k

;WITH fst(n)
     AS (SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1 UNION ALL
         SELECT 1),
     scd(n)
     AS (SELECT a.n
         FROM   fst a
                CROSS JOIN fst b),
     data(n)
     AS (SELECT a.n
         FROM   scd a
                CROSS JOIN (SELECT 1 a
                            UNION ALL
                            SELECT 1) b)
Insert into second_table
SELECT b.*
FROM   yourtable b
       CROSS JOIN data

Providing some context about what you're doing in the query would help — NickyvV, Aug 26 '16 at 08:38
@NickyvV - Done, It is very simple concept so didn't explain much — Pரதீப், Aug 26 '16 at 08:55
that doesn't give any of the values given by the vb code though. — Cato, Aug 26 '16 at 09:04

doza · Answer 2 · 2016-08-26T09:29:10.797

This can be done properly set-based with a recursive CTE.

See http://www.sqlservercentral.com/articles/T-SQL/90955/ for some examples.

This should get you close - I've used temp tables, but you can just re-format and insert the results of the CTE into TABLE_AMORTISSEMENT.

Using your example loan data:

CREATE TABLE #TABLE_PRETS
   (N_CONTRAT INT,
    MONTANT_CREDIT DECIMAL (18,8),
    TAUX_ACTUEL DECIMAL (18,8),
    TAUX_ASSURANCE DECIMAL (18,8),
    TVA DECIMAL (18,8),
    DATE_DEBUT DATE,
    MENSUALITE DECIMAL (18,8),
    DUREE INT);

INSERT INTO #TABLE_PRETS VALUES (400083, 300000, 5.92997, 0.0036, 0.1, '2016-01-01', 4510.66, 84);


;WITH LOANS AS
(
    SELECT 
        loanID              =   P.N_CONTRAT
    ,   remainingBalance    =   P.MONTANT_CREDIT
    ,   interestRate        =   P.TAUX_ACTUEL / 100
    ,   insuranceRate       =   P.TAUX_ASSURANCE
    ,   taxRate             =   P.TVA
    ,   startDate           =   P.DATE_DEBUT
    ,   monthlyRate         =   ((P.TAUX_ACTUEL / 100) * (1 + (P.TVA)) + P.TAUX_ASSURANCE) / 12
    ,   monthlypayment      =   P.MENSUALITE
    ,   durationmonths      =   P.DUREE
    --, origstartingbalance =   ROUND(P.MONTANT_CREDIT,2)
    FROM #TABLE_PRETS P
),
X AS (
SELECT
    loanID              =   L.loanID
,   monthnum            =   1
,   startingBalance     =   CONVERT(DECIMAL(18,8),ROUND(L.remainingBalance,2))
,   principal           =   ROUND(L.monthlypayment - ROUND(L.remainingBalance,2)  * L.monthlyRate, 2)
,   interestPaymentBT   =   CONVERT(DECIMAL(18,8),ROUND(ROUND(L.remainingBalance,2)  * L.interestRate / 12, 2))
,   taxOnInterest       =   ROUND(ROUND(ROUND(L.remainingBalance,2)  * L.interestRate / 12, 2) * L.taxRate, 2)
,   insurancePayment    =   CONVERT(DECIMAL(18,8),ROUND(ROUND(L.remainingBalance,2)  * L.insuranceRate, 2))
,   remainingBalance    =   ROUND(ROUND(L.remainingBalance,2)  - ROUND(L.monthlypayment - ROUND(L.remainingBalance,2) * L.monthlyRate, 2), 2)
FROM LOANS L
UNION ALL
SELECT 
    loanID              =   X.loanID
,   monthnum            =   X.monthnum + 1
,   startingBalance     =   CONVERT(DECIMAL(18,8),ROUND(X.remainingBalance,2) )
,   principal           =   ROUND(L.monthlypayment - ROUND(X.remainingBalance,2)  * L.monthlyRate, 2)
,   interestPaymentBT   =   CONVERT(DECIMAL(18,8),ROUND(ROUND(X.remainingBalance,2)  * L.interestRate / 12, 2))
,   taxOnInterest       =   ROUND(ROUND(ROUND(X.remainingBalance,2)  * L.interestRate / 12, 2) * L.taxRate, 2)
,   insurancePayment    =   CONVERT(DECIMAL(18,8),ROUND(ROUND(X.remainingBalance,2)  * L.insuranceRate, 2))
,   remainingBalance    =   ROUND(ROUND(X.remainingBalance,2)  - ROUND(L.monthlypayment - ROUND(X.remainingBalance,2) * L.monthlyRate, 2), 2)
FROM X
INNER JOIN LOANS L ON X.loanID = L.loanID
WHERE X.monthnum < L.durationmonths
)
SELECT * FROM X

Thank you for this elaborate answer. Everybody suggested using "recursive CTE" which I don't know. I'm reading about it right now and I will come back to this. — Naucle, Aug 26 '16 at 09:29

SQL query to generate multiple records from every record of a table

2 Answers2

Linked