SQL查询从表的每条记录生成多条记录

Question

我有一个包含 3000 条记录的表，使用其中的每一条记录，我必须生成大约 200 条记录(总共 600k 条记录)，并通过 SQL Server 2012 将它们插入到第二个表中。

我尝试使用 VBA 执行此操作(从第一个表中选择数据，计算，然后插入到第二个表中)，但插入速度太慢，并且需要很长时间。有人建议我直接在 SQL 中进行计算/插入，这就是我想要做的，但我对这门语言不是很熟悉。

下面是 VBA 代码:

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

到目前为止，我在 SQL 方面已经取得了进展，但我陷入了困境:

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

问题是，我不知道 SQL 是否可以一次计算整个列(如 Matlab)，或者我是否必须循环遍历列中的值。

第一个表 DATA 的示例:

第一个表数据的结果示例(实际上 84 行):

Answer 1

不知道为什么要这样做，但不需要使用 CURSOR 来执行此操作。尝试这个基于 SET 的方法

首先使用下面的堆叠 CTE 生成 200 条记录。使用前 10 条记录交叉连接它们以生成虚拟 200 条记录。执行每个CTE以了解结果(生成了多少条记录)。

;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

生成 200 条虚拟(1)数据记录后，交叉连接原始表以获得 CTE 数据和原始表的笛卡尔积(即)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