SQL Server动态匹配多表同名列实现ERP采购数据自动入库技术问询

核心动态字段匹配逻辑 #

你需求的核心是动态获取多表交集字段，无需硬编码即可适配ERP新增字段，实现逻辑如下：

交集字段查询方法 #

从INFORMATION_SCHEMA.COLUMNS中查询两个/多个表的共有字段，语句示例：

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
  -- 替换为目标表和所有源表的名称、对应Schema
  TABLE_NAME IN ('ERP采购头目标表', '自有采购头辅助表', '会计数据表', '公司数据表', '供应商数据表')
  AND TABLE_SCHEMA = '你的业务Schema'
GROUP BY COLUMN_NAME
HAVING COUNT(*) >= 2 -- 只要在目标表和任意一个源表中同时存在就纳入取数范围
ORDER BY ORDINAL_POSITION;

如果需要严格匹配两个特定表的交集，用以下写法：

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '表A' AND TABLE_SCHEMA = 'Schema'
INTERSECT
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '表B' AND TABLE_SCHEMA = 'Schema'

由于你提到所有表同名字段的数据类型完全一致，不需要做额外的类型转换，也不存在unicode转义问题，直接按优先级取数即可。

完整业务实现流程 #

整个逻辑封装在数据库存储过程中，自带事务、重试能力，无需上层编程语言参与：

1. 事务封装 #

所有操作包裹在事务中，出现异常自动回滚，自有辅助表的处理状态位保持未成功状态，支持后续重试，避免脏数据。

2. 采购头生成步骤 #

• 先锁定计数器表对应记录，避免并发冲突，生成新单号：

DECLARE @FiscalYear INT, @Series VARCHAR(10), @NewOrderNumber INT, @CurrentExternalId VARCHAR(50) = '待处理的外部单号';
-- 取待处理单的财年、序列信息
SELECT @FiscalYear = FiscalYear, @Series = Series FROM 自有采购头辅助表 WHERE ExternalOrderId = @CurrentExternalId AND 处理状态 = 0;
-- 原子更新计数器并获取新单号
UPDATE 计数器表 
SET LastNumber = LastNumber + 1, @NewOrderNumber = LastNumber + 1
WHERE Type = 'SupplierPurchase' AND FiscalYear = @FiscalYear AND Series = @Series;

• 动态构造插入SQL，按优先级取数（优先级从低到高：会计数据表→公司数据表→供应商数据表→自有辅助表，同名字段取高优先级表的值）：

DECLARE @ColumnList NVARCHAR(MAX), @InsertSQL NVARCHAR(MAX);
-- 拼接动态字段列表，按优先级用COALESCE取值
SELECT @ColumnList = STRING_AGG('COALESCE(辅助表.' + COLUMN_NAME + ', 供应商表.' + COLUMN_NAME + ', 公司表.' + COLUMN_NAME + ', 会计表.' + COLUMN_NAME + ')', ',')
FROM (
  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ERP采购头目标表' AND TABLE_SCHEMA = '业务Schema'
  INTERSECT
  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('自有采购头辅助表','会计数据表','公司数据表','供应商数据表') AND TABLE_SCHEMA = '业务Schema'
) t;
-- 拼接完整插入语句
SET @InsertSQL = '
INSERT INTO ERP采购头目标表 (FiscalYear, Series, Number, ExternalPurchaseID, ' + @ColumnList + ')
SELECT 
  @FiscalYear,
  @Series,
  @NewOrderNumber,
  @CurrentExternalId,
  ' + @ColumnList + '
FROM 自有采购头辅助表 辅助表
LEFT JOIN 会计数据表 会计表 ON 辅助表.SupplierCode = 会计表.SupplierCode
LEFT JOIN 公司数据表 公司表 ON 辅助表.SupplierCode = 公司表.SupplierCode
LEFT JOIN 供应商数据表 供应商表 ON 辅助表.SupplierCode = 供应商表.SupplierCode
WHERE 辅助表.ExternalOrderId = @CurrentExternalId
';
-- 执行动态SQL
EXEC sp_executesql @InsertSQL, N'@FiscalYear INT, @Series VARCHAR(10), @NewOrderNumber INT, @CurrentExternalId VARCHAR(50)', @FiscalYear, @Series, @NewOrderNumber, @CurrentExternalId;

3. 采购行生成步骤 #

完全复用上述动态字段逻辑，只需替换源表为：已生成的ERP采购头表、商品表、商品附加信息表、自有采购行辅助表，按ArticleCode关联，按优先级取数后批量插入ERP采购行目标表即可。

4. 状态更新 #

所有操作执行无异常后，更新自有采购头、采购行辅助表的处理状态位为成功，提交事务即可。

方案优势 #

• 完全动态适配：ERP新增字段后，只需在任意源表同步新增同名字段，存储过程自动识别纳入取数范围，无需修改代码
• 高性能：所有逻辑在数据库层执行，无网络开销，批量操作效率高
• 事务性：全程事务管控，支持失败重试，不会出现部分写入的异常情况

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