Dynamically Insert into Table A Based on Row_Num from Table B

I’ve condensed some data into TableB which looks like the following:

+========+==========+=========+
| AreaID | AreaName | Row_Num | 
+========+==========+=========+
|   506  | BC-VanW  |    1    |
+--------+----------+---------+
|   3899 | BC-VicS  |    2    |
+--------+----------+---------+
|   1253 | AB-CalW  |    3    |
+--------+----------+---------+

There are 2000 unique rows in total, Row_Num from 1 to 2000, and every AreaID in this table is naturally unique as well.

I now want to insert into a blank table, TableA, which has the following columns:

+========+==========+=========+============+===========+
| AreaID | StartDT  |  EndDT  | MarketCode |Allocation |
+========+==========+=========+============+===========+

The insert statement I want to use is repeats everything except for the AreaID

I was attempting some things earlier and this is a basic look at what I have that I’m hoping Stackoverflow could help me expand on:

DECLARE @AreaID NVARCHAR(4)
SET @AreaID = (SELECT AreaID FROM TableB WHERE Row_Num = 1)

DECLARE @Sql NVARCHAR(MAX)

SET @Sql = N'                   
INSERT INTO TableA (AreaID, StartDt, EndDt, MarketCode, Allocation) VALUES ('+@AreaID+', ''2020-11-01 00:00:00.000'', ''2049-12-31 00:00:00.000'' , 31 , 25.00);
INSERT INTO TableA (AreaID, StartDt, EndDt, MarketCode, Allocation) VALUES ('+@AreaID+', ''2020-11-01 00:00:00.000'', ''2049-12-31 00:00:00.000'' , 38 , 60.00);
INSERT INTO TableA (AreaID, StartDt, EndDt, MarketCode, Allocation) VALUES ('+@AreaID+', ''2020-11-01 00:00:00.000'', ''2049-12-31 00:00:00.000'' , 39 , 15.00);
'
EXEC sp_executesql @Sql
GO

From here I would want it to ‘loop’ through the Row_Nums, once and once only, and run the full insert query above eventually doing it for all 2000 Row_Nums.

Of course, if there is a more efficient way please let me know and I will take a look.

Thanks!

Answer

I think you want a cross join with a fixed list of values:

INSERT INTO TableA (AreaID, StartDt, EndDt, MarketCode, Allocation)
SELECT b.AreaID, x.*
FROM tableB b
CROSS APPLY (VALUES 
    ('2020-11-01 00:00:00.000', '2049-12-31 00:00:00.000', 31, 25.00),
    ('2020-11-01 00:00:00.000', '2049-12-31 00:00:00.000', 38, 60.00),
    ('2020-11-01 00:00:00.000', '2049-12-31 00:00:00.000', 39, 15.00)
) x(StartDt, EndDt, MarketCode, Allocation)

If the date range is always the same, this can be simplified a little:

INSERT INTO TableA (AreaID, StartDt, EndDt, MarketCode, Allocation)
SELECT b.AreaID, '2020-11-01 00:00:00.000', '2049-12-31 00:00:00.000', x.*
FROM tableB b
CROSS APPLY (VALUES 
    (31 , 25.00),
    (38 , 60.00),
    (39 , 15.00)
) x(MarketCode, Allocation)