如何在单个语句中将 CSV 数据拆分并插入到新表中? [英] how to split and insert CSV data into a new table in single statement?

问题描述

我有一个名为Documents"的表，其中包含如下一列:

I have a table named "Documents" containing a column as below:

文档 ID

我的数据格式为 - @DocID = 1,2,3,4

I have data in the format - @DocID = 1,2,3,4

如何使用单个查询将这些文档 ID 插入不同的行?

How do I insert these documentID's in separate rows using a single query?

推荐答案

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

You need a way to split and process the string in TSQL, there are many ways to do this. This article covers the PROs and CONs of just about every method:

SQL Server 2005 及更高版本中的数组和列表

您需要创建一个拆分函数.拆分函数的使用方法如下:

You need to create a split function. This is how a split function can be used:

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

我更喜欢使用数字表方法在 TSQL 中拆分字符串 - 使用一个数字表，但在 SQL Server 中有多种拆分字符串的方法，请参阅上一个链接，其中解释了每种方法的优点和缺点.

I prefer the number table approach to split a string in TSQL - Using a Table of Numbers but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

要使 Numbers Table 方法起作用，您需要进行一次时间表设置，这将创建一个表 Numbers，其中包含从 1 到 10,000 的行:

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 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 表后，创建此拆分函数:

Once the Numbers table is set up, create this split function:

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 字符串拆分为一个表格并在其上加入:

You can now easily split a CSV string into a table and join on it:

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

输出:

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,2,3,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

输出:

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

(6 row(s) affected)

如果您不想创建 Numbers 表并且正在运行 SQL Server 2005 及更高版本，则可以使用此拆分功能(不需要 Numbers 表):

if you don't want to create a Numbers tableand are running SQL Server 2005 and up, you can just use this split function (no Numbers table required):

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,CHARINDEX(@SplitOn,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

这篇关于如何在单个语句中将 CSV 数据拆分并插入到新表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！