SQL Server 2019如何复制首行数据到多行并从其他表更新列值

现有SQL Server 2019环境下的两张表：

• Table 1：需更新的源表，ID为自增标识列，首行数据已通过API接收UI输入完成更新。需求是将该行的col1、col2值复制到多行，同时从Table 2中获取对应的date和volume列值，最终生成目标格式的数据。
• Table 2：提供date和volume的参考数据。

Table 1初始数据 #

Table 2数据 #

期望Table 1最终数据 #

已尝试的SQL语句 #

insert into table1 (capId, [date], volume)
(
   select a.capId AS capId,
      a.[date] AS [date] , a.volume AS volume,
   from
   (
      Select s.*,
         row_number() over(order by [date]) rn
      from table2 s
      where capId = 2
   ) a
   where rn >1
)

该语句未复制Table 1首行的col1、col2值，无法满足需求。

要解决这个问题，需要从Table 1中获取已存在行的col1、col2值，再关联Table 2中对应capId的后续数据进行插入。以下是两种可行的完整SQL语句：

方案一：通过日期排除重复行 #

INSERT INTO Table1 (capId, [date], col1, col2, volume)
SELECT 
    t2.capId,
    t2.[date],
    t1.col1,
    t1.col2,
    t2.volume
FROM Table2 t2
-- 关联Table1中已完成更新的目标行，获取col1和col2
JOIN (
    SELECT capId, col1, col2 
    FROM Table1 
    WHERE ID = 1 -- 若需适配多capId场景，可改为TOP 1 ORDER BY ID
) t1 ON t2.capId = t1.capId
-- 排除Table2中已在Table1存在的日期数据，避免重复插入
WHERE t2.[date] NOT IN (SELECT [date] FROM Table1 WHERE capId = t2.capId);

方案二：通过行号筛选后续数据 #

INSERT INTO Table1 (capId, [date], col1, col2, volume)
SELECT 
    a.capId,
    a.[date],
    t1.col1,
    t1.col2,
    a.volume
FROM (
    SELECT s.*,
           ROW_NUMBER() OVER(ORDER BY [date]) rn
    FROM Table2 s
    WHERE capId = 2
) a
-- 关联Table1首行获取col1、col2值
JOIN (SELECT col1, col2 FROM Table1 WHERE ID = 1) t1 
  ON a.capId = (SELECT capId FROM Table1 WHERE ID = 1)
-- 只取Table2中除第一行外的后续数据
WHERE a.rn > 1;

逻辑说明 #

两种方案核心都是从Table1获取已有的col1、col2值，再结合Table2的date和volume数据插入新行：

1. 方案一通过日期判断避免重复插入，适配Table1可能存在多日期的场景；
2. 方案二通过行号严格筛选Table2的后续数据，适合需要严格按Table2顺序插入的场景。

内容的提问来源于stack exchange，提问作者Chaitra Murthy