咨询SQL Server自动导入CSV并添加额外列的脚本逻辑与步骤

我正在开发一套可每日自动将CSV文件数据导入SQL Server表的系统，目标是构建table-master和table-data两张表：table-master存储成功导入的文件信息，table-data存储CSV导入的数据。
但table-data包含CSV文件中没有的额外列，这些列的关联信息需同步至table-master。
我已通过Bulk Insert实现CSV导入SQL Server，但不清楚如何为导入后的表添加额外列。

table-master列定义 #

Id = 自动生成（建议设为IDENTITY自增列）
Name-file = 格式为REGISTRATION-yyyymmdd-hhmmss
User_process_date
Create Date = 处理日期
Create By = 默认值为system
Change Date = 处理日期
Change By = 默认值为system

table-data列组成 #

1-20：来自CSV文件的列
21：id = 自动生成，且每个CSV文件导入完成后重置为1
22：id-header = 与`table-master`中使用的Id一致
23. Create Date = 处理日期
24. Create By = 默认值为system
25. Change Date = 处理日期
26. Change By = 默认值为system

请问该脚本的逻辑是什么？我应从何处着手？

核心脚本执行逻辑 #

1. 创建临时过渡表
   先建立一个与CSV结构完全匹配的临时表（如#TempCSVData），仅包含CSV的20列，用于接收Bulk Insert的原始数据。这一步是为了分离原始数据导入和额外列填充的操作，避免直接操作目标表出错。

2. 生成主表记录并获取关联ID
   在导入CSV数据前，先向table-master插入一条记录，生成唯一的自增Id。通过SCOPE_IDENTITY()获取该Id，作为后续table-data中id-header的关联值。
   示例代码：

   DECLARE @MasterId INT;
   DECLARE @CurrentProcessDate DATETIME = GETDATE();
   DECLARE @GeneratedFileName NVARCHAR(100) = 'REGISTRATION-' + FORMAT(@CurrentProcessDate, 'yyyyMMdd-HHmmss');
   
   -- 插入主表记录
   INSERT INTO table-master ([Name-file], [User_process_date], [Create Date], [Create By], [Change Date], [Change By])
   VALUES (@GeneratedFileName, @CurrentProcessDate, @CurrentProcessDate, 'system', @CurrentProcessDate, 'system');
   
   -- 获取刚生成的主表Id
   SET @MasterId = SCOPE_IDENTITY();
   

3. 导入并填充目标数据表
   将临时表中的原始数据插入table-data，同时为额外列赋值：

   • 列21（文件内自增id）：用ROW_NUMBER() OVER (ORDER BY (SELECT NULL))生成，确保每个文件的导入记录从1开始计数。
   • 列22（id-header）：直接使用前面获取的@MasterId，建立与主表的关联。
   • 列23-26：用当前处理日期和默认值'system'填充。
     示例代码：

   -- 假设CSV的20列名为col1到col20，需替换为实际列名
   INSERT INTO table-data ([col1], [col2], ..., [col20], [id], [id-header], [Create Date], [Create By], [Change Date], [Change By])
   SELECT 
       [col1], [col2], ..., [col20],
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [id],
       @MasterId AS [id-header],
       @CurrentProcessDate AS [Create Date],
       'system' AS [Create By],
       @CurrentProcessDate AS [Change Date],
       'system' AS [Change By]
   FROM #TempCSVData;
   

4. 清理临时资源
   导入完成后删除临时表，释放资源：

   DROP TABLE IF EXISTS #TempCSVData;
   

着手实施的优先级建议 #

• 确认表结构合法性：确保table-master的Id设为IDENTITY(1,1)自增列；table-data的列21不要设为自增，而是通过插入时的行号生成。
• 单流程测试验证：手动执行一次完整的导入流程，检查table-master和table-data的关联是否正确，额外列的值是否符合预期。
• 添加错误处理机制：用TRY...CATCH块包裹整个逻辑，若导入失败则回滚主表的插入操作，避免产生无效的主表记录。
• 配置自动化调度：测试通过后，使用SQL Server Agent创建定时作业，设置每日自动执行该导入脚本。

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