创建与永久表列结构和数据类型一致的内存表的最佳方法

实现方案 #

方案1：使用内存优化临时表（最优选择，无需手动定义字段） #

如果业务场景允许使用#开头的临时表替代@开头的表变量，这是成本最低的方案，内存优化的临时表性能和普通表变量基本一致，还支持更多操作特性：

-- 直接生成和原表结构完全一致的空临时表
SELECT TOP 0 *
INTO #mymemorytable
FROM myrealtable

SQL Server 2014及以上版本支持配置临时表内存优化，开启后上述临时表会完全驻留内存，没有磁盘IO开销。

方案2：自动生成表变量声明脚本（必须使用@表变量场景） #

如果你必须使用@开头的表变量，也不需要手动逐个定义字段，运行以下脚本可以自动生成对应原表结构的DECLARE语句：

DECLARE @TableName SYSNAME = 'myrealtable' -- 替换为你的实际表名
DECLARE @SQL NVARCHAR(MAX) = 'DECLARE @mymemorytable TABLE (' + 
STRING_AGG(QUOTENAME(c.name) + ' ' + 
    CASE WHEN t.name IN ('varchar', 'nvarchar', 'char', 'nchar') 
        THEN t.name + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length/CASE WHEN t.name IN ('nvarchar', 'nchar') THEN 2 ELSE 1 END AS VARCHAR) END + ')'
        WHEN t.name IN ('decimal', 'numeric') THEN t.name + '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
        ELSE t.name END 
    + CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END, ', ')
+ ')'
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@TableName)

PRINT @SQL

运行后从消息面板复制生成的DECLARE语句直接执行，即可得到结构和原表完全匹配的表变量。

注：如果使用SQL Server 2016及更低版本，将上述脚本中的STRING_AGG部分替换为FOR XML PATH的字符串拼接逻辑即可，核心逻辑都是从系统元数据表自动拉取原表字段定义，避免手动输入错误。

错误原因说明 #

• SELECT INTO @mymemorytable语法不成立：SQL Server的SELECT INTO语法仅支持创建普通物理表、#本地临时表、##全局临时表，不支持直接创建@开头的表变量。
• declare @mymemorytable as table();语法不成立：TABLE()的括号内必须明确填写字段名、字段类型、可空性等定义，不可留空。

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