使用CTE拆分星号分隔文件插入SQL Server遇tempdb空间不足问题

我有一个星号分隔的平面文件，先导入到单列表格中做错误校验，确认无误后需要拆分并插入到包含17列的目标表（对应分隔后的每个字段）。

示例数据：

5*0121**01300421142116*CW*WAREHOUSE AND PREMISES*8497395000*01-APR-2016****18017262000*25-APR-2016*096G*26621354212*25-APR-2016*17-MAY-2016*

我使用以下CTE语句进行拆分和插入：

WITH cte AS 
(
    SELECT 
        check_content,
        SUBSTRING(check_content, 1, ISNULL(NULLIF(CHARINDEX('*', check_content), 0) - 1, LEN(check_content))) sString,
        NULLIF(CHARINDEX('*', check_content), 0) cIndex,
        1 Lvl
    FROM   
        delimiter_check_reval T
    UNION ALL
    SELECT 
        check_content,
        SUBSTRING(check_content, cindex + 1, ISNULL(NULLIF(CHARINDEX('*', check_content, cindex + 1), 0) - 1 - cindex, LEN(check_content))),
        NULLIF(CHARINDEX('*', check_content, cindex + 1), 0),
        lvl + 1
    FROM   
        cte
    WHERE  
        cindex IS NOT NULL
)
INSERT INTO raw_epoch_historic
            (revaluation_id,
             billing_authority_code,
             ndr_community_code,
             ba_reference_number,
             primary_and_secondary_description_code,
             primary_description_text,
             unique_address_reference_number,
             effective_date,
             composite_indicator,
             rateable_value,
             appeal_settlement_code,
             assessment_reference,
             list_alteration_date,
             scat_code_and_suffix,
             case_number,
             historic_from_date,
             historic_to_date)
SELECT Max(CASE
             WHEN lvl = 1 THEN sstring
           END) val1,
       Max(CASE
             WHEN lvl = 2 THEN sstring
           END) val2,
       Max(CASE
             WHEN lvl = 3 THEN sstring
           END) val3,
       Max(CASE
             WHEN lvl = 4 THEN sstring
           END) val4,
       Max(CASE
             WHEN lvl = 5 THEN sstring
           END) val5,
       Max(CASE
             WHEN lvl = 6 THEN sstring
           END) val6,
       Max(CASE
             WHEN lvl = 7 THEN sstring
           END) val7,
       Max(CASE
             WHEN lvl = 8 THEN sstring
           END) val8,
       Max(CASE
             WHEN lvl = 9 THEN sstring
           END) val9,
       Max(CASE
             WHEN lvl = 10 THEN sstring
           END) val10,
       Max(CASE
             WHEN lvl = 11 THEN sstring
           END) val11,
       Max(CASE
             WHEN lvl = 12 THEN sstring
           END) val12,
       Max(CASE
             WHEN lvl = 13 THEN sstring
           END) val13,
       Max(CASE
             WHEN lvl = 14 THEN sstring
           END) val14,
       Max(CASE
             WHEN lvl = 15 THEN sstring
           END) val15,
       Max(CASE
             WHEN lvl = 16 THEN sstring
           END) val16,
       Max(CASE
             WHEN lvl = 17 THEN sstring
           END) va1l7
--, datastring OriginalDataString
FROM   cte
GROUP  BY check_content;

问题现象 #

该语句处理少量数据时运行正常，但处理全量250万行数据时，触发如下错误：

警告: 聚合或其他 SET 操作消除了空值。无法为数据库 'tempdb' 中的对象 'dbo.WORKFILE GROUP large record overflow storage: 140739078979584' 分配空间，因为 'PRIMARY' 文件组已满。请通过删除不需要的文件、删除文件组中的对象、向文件组添加其他文件或为文件组中的现有文件启用自动增长来创建磁盘空间。

插入操作失败，未插入任何数据。

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