CREATE NONCLUSTERED INDEX [cbids_index]
ON [dbo].[TousseInstance] ([classifyBasketIds])
INCLUDE ([id]);
--防止增量的原因存在数据
delete from ClassifyBasket_TousseInstance;
WITH SplitStrings AS (    
    SELECT     
        TI.id,    
        LEFT(TI.classifyBasketIds, CHARINDEX(';', TI.classifyBasketIds + ';') - 1) AS basket_id,    
        CASE   
            WHEN CHARINDEX(';', TI.classifyBasketIds) > 0 THEN  
                RIGHT(TI.classifyBasketIds, LEN(TI.classifyBasketIds) - CHARINDEX(';', TI.classifyBasketIds))  
            ELSE  
                ''  
        END AS remaining    
    FROM     
        TousseInstance TI    
    WHERE     
    	TI.operationTime between '2024-01-01 00:00:00.000' and '2025-01-01 00:00:00.000'
        and TI.classifyBasketIds IS NOT NULL AND TI.classifyBasketIds <> ''
    UNION ALL   
    SELECT     
        s.id,    
		LEFT(s.remaining, CHARINDEX(';', s.remaining + ';') - 1)   as basket_id,  
        CASE   
            WHEN CHARINDEX(';', s.remaining ) > 0 AND LEN(s.remaining) > CHARINDEX(';', s.remaining) THEN  
                RIGHT(s.remaining, LEN(s.remaining) - CHARINDEX(';', s.remaining))  
            ELSE  
                ''  
        END AS remaining    
    FROM     
        SplitStrings s    
    WHERE     
        s.remaining <> ''  -- 确保 remaining 不为空字符串时才继续递归  
),    
TrimmedValues AS (    
    SELECT     
        id,    
        CAST(NULLIF(LTRIM(RTRIM(basket_id)), '') AS INT) AS basket_id  -- 使用 NULLIF 来处理空字符串  
    FROM     
        SplitStrings    
    WHERE    
        basket_id <> ''  -- 排除空字符串或仅包含空格的 basket_id  
	union all 
	select id,classifyBasket_id basket_id 
	from TousseInstance 
	where
	operationTime between '2024-01-01 00:00:00.000' and '2025-01-01 00:00:00.000'
	and classifyBasket_id is not null
)    

INSERT INTO ClassifyBasket_TousseInstance (tousseInstanceId, classifyBasketId)  
SELECT   
    distinct id,  
    basket_id  
FROM   
    TrimmedValues;
drop index TousseInstance.cbids_index;