Partition Switching Issue

Question

Below is my stored procedure

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

The MU_DIM_UNPVT_LOAD table has the same structure and partition as the temporary table. When I execute this procedure, I get this error:

Msg 4904, Level 16, State 1, Line 5 ALTER TABLE SWITCH statement failed. The specified partition 2 of target table 'DB_32f2002bc6bd44c6b4b90a1425296ffe.dbo.Table_b6dae0230e604f9195c4a93ca98ad3d1_E' must be empty.

The UNPVT table gets loaded with data for two ids i.e. 1 and 2 and then it fails. I'm not sure what's the issue. Please advice.

(Please Note: I'm working in PDW)

Answer 1

Switch it out with an empty table first, and then switch in the populated table. Don't just try to swap the existing partition with a full populated table.