多重CSV拆分存储过程

本文关键字：存储过程 拆分 CSV 多重 | 更新日期: 2024-06-14 02:03:56

我想将用户详细信息保存到具有列ID, UserName, Age的表中。

我对存储过程的输入将采用这种格式（即CSVUserName、CSVAge。）

'ABC,XYZ,MNO', '12,34,22'

我的存储过程是

CREATE PROC [dbo].[PROCabc]
(@valueListA text, @valueListb text) 
AS 
    DECLARE @posA INT = 0, @lenA INT = 0, @valueA varchar(8000),
            @posb INT = 0, @lenb INT = 0, @valueb varchar(8000)
    WHILE CHARINDEX(',', @valueListA, @posA+1) and CHARINDEX(',', @valueListB, @posB+1)>0
    BEGIN
         set @lenA = CHARINDEX(',', @valueListA, @posA+1) - @posA
         set @valueA = SUBSTRING(@valueListA, @posA, @lenA)
         set @lenb = CHARINDEX(',', @valueListb, @posb+1) - @posb
         set @valueb = SUBSTRING(@valueListb, @posb, @lenb)
         INSERT INTO USERDETAILS (Name, Age)
         VALUES(@valueA, @valueb)
         set @posA = CHARINDEX(',', @valueListA, @posA+@lenA) +1
         set @posb = CHARINDEX(',', @valueListb, @posb+@lenb) +1
   END

这是正确的吗？或者我们有其他方法让它变得简单。

多重CSV拆分存储过程

您可以使用XML格式将CSV转换为行，以简化解决方案。它也将具有良好的可读性。我已经把逻辑写在里面了。

 CREATE PROC [dbo].[PROCabc]
    (@valueListA VARCHAR(MAX), @valueListb VARCHAR(MAX)) 
    AS    
    BEGIN 
        ;WITH NAME AS
        (
            -- Convert to rows and bring row number in the order of default order
            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0))RNO,
            LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'NAMES' 
            FROM  
            (                   
                 SELECT CAST ('<M>' + REPLACE(@valueListA, ',', '</M><M>') + '</M>' AS XML) AS Data         
            ) AS A 
            CROSS APPLY Data.nodes ('/M') AS Split(a)
        )
        ,AGE AS
        (
            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0))RNO,
            LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'AGES' 
            FROM  
            (                    
                 SELECT CAST ('<M>' + REPLACE(@valueListb, ',', '</M><M>') + '</M>' AS XML) AS Data         
            ) AS A 
            CROSS APPLY Data.nodes ('/M') AS Split(a)
        )
        -- Since we have same order of Row number, we join to get the data for each Age for corresponding Name
        INSERT INTO USERDETAILS (Name, Age)
        SELECT N.NAMES,A.AGES
        FROM NAME N
        JOIN AGE A ON N.RNO=A.RNO
    END
  • 单击此处查看结果
相关文章：