Insert a single line of data into multiple rows of table

Question

Synopsis: How to write the SQL code to insert one record from a flat-file into separate records to be inserted into a SQL Server table.

Specifics: I have a table to record lease payments. For each rental agreement the due date is always the first day of the month. There is an entry in the table for each month for the duration of the lease. For simplicity’s sake let’s say that there is a lease contract that runs from September 1, 2019 to August 31, 2020. The table would look like this:

+------------------------------------------------------+
| CONTRACT_ID | DUE_DATE_DT | INVOICE_ID | RENTAL_AMT  |
+-------------+-------------+------------+-------------+
| 12345       |    9/1/2019 |            |             |
+-------------+-------------+------------+-------------+
| 12345       |   10/1/2019 |            |             |
+-------------+-------------+------------+-------------+
...
+-------------+-------------+------------+-------------+
| 12345       |    8/1/2020 |            |             |
+-------------+-------------+------------+-------------+

The invoices are normally for one calendar month, but sometimes there can be two or three months because the vendor is playing catch up. The data is received in a flat file, a sample would look like:

Contract  Rental Period From Date  Rental Period To Date  Invoice Number   Amount
31125      9/1/2019                  9/30/2019             6378            400.00  (standard scenario)
12345      9/1/2019                 11/30/2019             789             150.00  (multi-month scenario)

The data from the flat file needs to be stored in three separate records in the table:

+------------------------------------------------------+
| CONTRACT_ID | DUE_DATE_DT | INVOICE_ID | RENTAL_AMT  |
+-------------+-------------+------------+-------------+
| 12345       |    9/1/2019 |   789      |   50.00     |
+-------------+-------------+------------+-------------+
| 12345       |   10/1/2019 |   789      |   50.00     |
+-------------+-------------+------------+-------------+
| 12345       |   11/1/2019 |   789      |   50.00     |
+-------------+-------------+------------+-------------+

Thank you in advance.

Answer 1

You can try to use a recursive CTE. Consider this:

CREATE TABLE #RawData(Contract int, Rental_Period_From Date, Rental_Period_To Date, Invoice_Number int, Amount Decimal(10,2))
INSERT INTO #RawData VALUES(31225, '9/1/2019','9/30/2019', 6378,400.00)
INSERT INTO #RawData VALUES(12345, '9/1/2019','11/30/2019', 789,150.00)


;WITH CTE
AS
(
select Contract,Rental_Period_From DueDate,Invoice_Number,Amount, 0 MonthCounter, Rental_Period_To from #RawData
UNION ALL
SELECT Contract, DATEADD(month,MonthCounter+1,DueDate),Invoice_Number,Amount, MonthCounter,Rental_Period_To   from CTE
WHERE DATEADD(month,MonthCounter+1,DueDate) <= Rental_Period_To

)
SELECT CONTRACT, DueDate,Invoice_Number, CAST(Amount / (SELECT COUNT(*) 
                FROM CTE t1 WHERE t1.Contract = t2.Contract ) as decimal(10,2)) Amount 
FROM CTE t2