将各种不同表头的数据存放到同一个表的探讨

USE tempdb
GO
IF OBJECT_ID('tab_header') IS NOT NULL
	DROP TABLE tab_header
GO
IF OBJECT_ID('tab_data') IS NOT NULL
	DROP TABLE tab_data
GO
CREATE TABLE tab_header(
	headerId INT IDENTITY(1,1) PRIMARY KEY,
	tabFlagId INT NOT NULL,
	colId INT NOT NULL,
	colName NVARCHAR(50) NOT NULL
)
GO
CREATE TABLE tab_data(
	dataId INT IDENTITY(1,1) PRIMARY KEY,
	tabFlagId INT NOT NULL,
	c1 NVARCHAR(MAX) NOT NULL DEFAULT(''),
	c2 NVARCHAR(MAX) NOT NULL DEFAULT(''),
	c3 NVARCHAR(MAX) NOT NULL DEFAULT(''),
	c4 NVARCHAR(MAX) NOT NULL DEFAULT(''),
	c5 NVARCHAR(MAX) NOT NULL DEFAULT(''),
	c6 NVARCHAR(MAX) NOT NULL DEFAULT('')
)
GO
--注：tabFlagId 可用序列代替，并发不容易出问题
DECLARE @tabFlagId INT
SELECT @tabFlagId=ISNULL((SELECT MAX(tabFlagId) FROM tab_header ),0)+1

SELECT * 
INTO #tmp
FROM 
(
	SELECT 1 AS a,2 AS b 
	UNION ALL
	SELECT 3 AS a,3 AS b
) AS t

INSERT INTO tab_header
(
	tabFlagId,
	colId,
	colName
)
SELECT @tabFlagId,
	   c.column_id,
	   c.name 
FROM sys.[columns] AS c WHERE c.[object_id]=OBJECT_ID('#tmp')

INSERT INTO tab_data
(
	-- dataId -- this column value is auto-generated
	tabFlagId,
	c1,
	c2
)
SELECT @tabFlagId AS tabFlagId,
a,
b
FROM #tmp AS t
GO
--要查 tabFlagId=1 的数据
DECLARE @tabFlagId INT,@colNames NVARCHAR(MAX),@sql NVARCHAR(MAX)
SET @tabFlagId=1
SET @sql='select '+STUFF((
SELECT ',c'+ltrim(th.colId)+' AS ['+th.colName+']' 
FROM tab_header AS th WHERE tabFlagId=1
FOR XML PATH('')),1,1,'')+' from tab_data where tabFlagId='+LTRIM(@tabFlagId);
PRINT @sql
EXEC(@sql)

其实上面这些，只有插入到 tab_data 有问题， 因为实际上执行时不知道具体有多少列。

实际操作中我是用程序中的 SqlBulkCopy 执行这一步的: DataTable 写入到表

另外，tab_data 实际上可以定义100列，基本上不可能超过就行。