sql-server – 如何在单个语句中将CSV数据拆分并插入到新表中？

我有一个名为“Documents”的表,其中包含如下列：

DocumentID

我有格式的数据 – @DIDID = 1,2,3,4

如何使用单个查询在单独的行中插入这些documentID？

解决方法

您需要一种在TSQL中拆分和处理字符串的方法,有很多方法可以做到这一点.本文涵盖几乎所有方法的PRO和CON：

Arrays and Lists in SQL Server 2005 and Beyond

您需要创建拆分功能.这是分割函数的使用方式：

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL – Using a Table of Numbers但是有很多方法可以在SQL Server中拆分字符串,请参阅前面的链接,它解释了每个链接的PRO和CON.

要使Numbers Table方法起作用,您需要执行一次性表设置,这将创建一个包含1到10,000行的表号：

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置Numbers表后,创建此拆分功能：

CREATE FUNCTION inline_split_me (@SplitOn char(1),@param varchar(7998)) RETURNS TABLE AS
   RETURN(SELECT substring(@SplitOn + @param + ',',Number + 1,charindex(@SplitOn,@SplitOn + @param + @SplitOn,Number + 1) - Number - 1)
                 AS Value
          FROM   Numbers
          WHERE  Number <= len(@SplitOn + @param + @SplitOn) - 1
            AND  substring(@SplitOn + @param + @SplitOn,Number,1) = @SplitOn)

GO

您现在可以轻松地将CSV字符串拆分为表格并加入其中：

select * from dbo.inline_split_me(';','1;22;333;4444;;') where LEN(Value)>0

OUTPUT：

Value
----------------------
1
22
333
4444

(4 row(s) affected)

让你新表使用这个：

--set up tables:
DECLARE @Documents table (DocumentID varchar(500),SomeValue varchar(5))
INSERT @Documents VALUES ('1,4','AAA')
INSERT @Documents VALUES ('5,6','BBBB')

DECLARE @NewDocuments table (DocumentID int,SomeValue varchar(5))

--populate NewDocuments
INSERT @NewDocuments
    (DocumentID,SomeValue)
SELECT
    c.value,a.SomeValue
    FROM @Documents    a
        CROSS APPLY dbo.inline_split_me(',a.DocumentID) c

 --show NewDocuments contents:
select * from @NewDocuments

OUTPUT：

DocumentID  SomeValue
----------- ---------
1           AAA
2           AAA
3           AAA
4           AAA
5           BBBB
6           BBBB

(6 row(s) affected)

如果您不想创建Numbers表并且正在运行SQL Server 2005及更高版本,则可以使用此split函数(不需要Numbers表)：

CREATE FUNCTION inline_split_me (@SplitOn char(1),@String varchar(7998))
RETURNS TABLE AS
RETURN (WITH SplitSting AS
           (SELECT
                LEFT(@String,CHARINDEX(@SplitOn,@String)-1) AS Part,RIGHT(@String,LEN(@String)-CHARINDEX(@SplitOn,@String)) AS Remainder
                WHERE @String IS NOT NULL AND CHARINDEX(@SplitOn,@String)>0
            UNION ALL
            SELECT
                LEFT(Remainder,Remainder)-1),RIGHT(Remainder,LEN(Remainder)-CHARINDEX(@SplitOn,Remainder))
                FROM SplitSting
                WHERE Remainder IS NOT NULL AND CHARINDEX(@SplitOn,Remainder)>0
            UNION ALL
            SELECT
                Remainder,null
                FROM SplitSting
                WHERE Remainder IS NOT NULL AND CHARINDEX(@SplitOn,Remainder)=0
           )
           SELECT Part FROM SplitSting
       )
GO