sql - 分区切换问题

Question

下面是我的存储过程

ALTER PROC MU_STG_LOAD AS

BEGIN

DECLARE @SQL VARCHAR(4000)
DECLARE @CREATE VARCHAR(4000)
DECLARE @ALTER VARCHAR(4000)
DECLARE @i INT = 1

WHILE @i <= 24
BEGIN


SET @SQL = 
'SELECT CLNT_ID, CLNT_NM,' + CAST(@i AS VARCHAR) + 'AS DT_ID,
CAST(VAL_' + CAST(@i AS VARCHAR) + ' AS DECIMAL(18,3)) AS TOT_QTY
FROM MU_DIM
WHERE CAST(VAL_'+ CAST(@i AS VARCHAR) + ' AS DECIMAL(18,3)) <> 0'

SET @CREATE = 
'CREATE TABLE MU_DIM_TMP
WITH (DISTRIBUTION=HASH(CLNT_ID),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION (DT_ID RANGE RIGHT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)))
AS '

EXEC (@CREATE + @SQL)

INSERT INTO MU_DIM_UNPVT_LOAD (CLNT_ID, CLNT_NM, DT_ID, TOT_QTY)
SELECT CLNT_ID, CLNT_NM, DT_ID, TOT_QTY FROM MU_DIM_TMP

SET @ALTER = 
'ALTER TABLE MU_DIM_TMP SWITCH PARTITION ' + CAST(@i AS VARCHAR) + ' TO MU_DIM_UNPVT_LOAD PARTITION ' + CAST(@i AS VARCHAR)

EXEC(@ALTER)

IF OBJECT_ID('MU_DIM_TMP') IS NOT NULL
DROP TABLE MMC_AMG_MU_D_LOAD_TMP

SET @i = @i + 1
END

GRANT EXECUTE ON MMC_AMG_MU_XPT_STG TO PUBLIC

END

MU_DIM_UNPVT_LOAD 表与临时表具有相同的结构和分区。当我执行这个过程时，我得到这个错误:

消息 4904，级别 16，状态 1，第 5 行ALTER TABLE SWITCH 语句失败。目标表 'DB_32f2002bc6bd44c6b4b90a1425296ffe.dbo.Table_b6dae0230e604f9195c4a93ca98ad3d1_E' 的指定分区 2 必须为空。

UNPVT 表加载了两个 ID(即 1 和 2)的数据，然后它失败了。我不确定是什么问题。请指教。

(请注意:我在 PDW 工作)

Answer 1

先用空表将其切换出去，然后再切换到已填充的表。不要只是尝试用一个完整的填充表交换现有分区。