Разделение разделительных значений в столбце SQL на несколько строк

Мне бы очень понравился совет, чтобы предоставить некоторую справочную информацию, с которой я работаю, вставляя журналы отслеживания сообщений из Exchange 2007 в SQL. Поскольку у нас есть миллионы и миллионы строк в день, я использую оператор Bulk Insert для вставки данных в таблицу SQL.

Фактически, я действительно Bulk Insert в временную таблицу, а затем оттуда Я СООБЩАЮ данные в таблицу live, это для проблем с анализируемым анализом, поскольку определенные поля в противном случае имеют кавычки и такие значения вокруг значений.

Это хорошо работает, за исключением того факта, что столбец адреса получателя - это поле с разделителями, разделенное символом a; характер, и он может быть невероятно длинным иногда, поскольку может быть много получателей электронной почты.

Я хотел бы взять этот столбец и разделить значения на несколько строк, которые затем будут вставлены в другую таблицу. Проблема в том, что я пытаюсь либо слишком долго, либо не работать так, как я хочу.

Возьмем следующие данные:

message-id                                              recipient-address
[email protected]   [email protected]
[email protected]     [email protected]
[email protected]              [email protected];[email protected];[email protected]

Я хотел бы, чтобы это было отформатировано, как указано в моей таблице получателей:

message-id                                              recipient-address
[email protected]   [email protected]
[email protected]     [email protected]
[email protected]              [email protected]
[email protected]              [email protected]
[email protected]              [email protected]

Есть ли у кого-нибудь идеи о том, как я могу это сделать?

Я хорошо знаю PowerShell, поэтому я попытался в этом, но цикл foreach даже на 28K-записих навсегда обрабатывался, мне нужно что-то, что будет работать как можно быстрее/эффективнее.

Спасибо!

Ответ 1

Сначала создайте функцию split:

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

Теперь вы можете экстраполировать просто:

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

Также я предлагаю не помещать тире в имена столбцов. Это означает, что вам всегда нужно помещать их в [square brackets].

Ответ 2

SQL Server 2016 включает новую функцию таблицы string_split(), аналогичную предыдущему решению.

Единственное требование - установить уровень совместимости до 130 (SQL Server 2016)