sql - 将 SQL 列中的分隔值拆分为多行

Question

我真的很想在这里得到一些建议，以提供一些我正在将邮件跟踪日志从 Exchange 2007 插入 SQL 的背景信息。由于我们每天有数百万行，因此我使用批量插入语句将数据插入 SQL 表中。

事实上，我实际上批​​量插入到临时表中，然后从那里将数据合并到实时表中，这是为了测试解析问题，因为某些字段在值周围有引号等。

这很有效，但收件人地址列是一个由 ; 分隔的分隔字段。字符，有时可能会非常长，因为可能有很多电子邮件收件人。

我想获取此列，并将值拆分为多行，然后将其插入到另一个表中。问题是我正在尝试的任何事情要么花费太长时间，要么无法按照我想要的方式工作。

以这个示例数据为例:

message-id                                              recipient-address
2D5E558D4B5A3D4F962DA5051EE364BE06CF37A3A5@Server.com   user1@domain1.com
E52F650C53A275488552FFD49F98E9A6BEA1262E@Server.com     user2@domain2.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user3@domain3.com;user4@domain4.com;user5@domain5.com

我希望在我的收件人表中将其格式化为以下格式:

message-id                                              recipient-address
2D5E558D4B5A3D4F962DA5051EE364BE06CF37A3A5@Server.com   user1@domain1.com
E52F650C53A275488552FFD49F98E9A6BEA1262E@Server.com     user2@domain2.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user3@domain3.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user4@domain4.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user5@domain5.com

有人对我如何做到这一点有任何想法吗？

我非常了解 PowerShell，所以我尝试了这一点，但是即使在 28K 记录上的 foreach 循环也需要很长时间才能处理，我需要一些能够尽可能快速/高效地运行的东西。

谢谢!

Answer 1

如果您使用的是 SQL Server 2016+

您可以使用新的 STRING_SPLIT 函数，我已在博客中介绍了该函数 here ，布伦特·奥扎 (Brent Ozar) 已在博客中介绍 here .

SELECT s.[message-id], f.value
  FROM dbo.SourceData AS s
  CROSS APPLY STRING_SPLIT(s.[recipient-address], ';') as f;

如果您仍在使用 SQL Server 2016 之前的版本

创建分割函数。这只是众多示例之一:

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

我已经讨论了其他一些here , here ，以及比一开始就 split 更好的方法 here .

现在您可以简单地推断:

SELECT s.[message-id], f.Item
  FROM dbo.SourceData AS s
  CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;

此外，我建议不要在列名称中添加破折号。这意味着您始终必须将它们放在[方括号]中。