Альтернатива функции разделения поворота курсора?

Итак, я создаю хранимую процедуру, конечной целью которой является динамическое решение ETL. Моя компания имеет дело с большим количеством сторонних данных, и часто я не знаю количества столбцов, типов данных, формата данных и т. д. Поэтому я собрал ряд временных таблиц с динамическими sql и операторы массовой вставки, чтобы получить данные в SQL Server. В настоящее время данные поступают в виде поля nvarchar с одним столбцом, разделенного табуляцией или вертикальной чертой, и часто в файле txt или csv содержится более 100 000 строк. Ниже приведен пример вышеупомянутого формата csv/txt:

RawSingleLine
9XX01 No Cancelled Inadvertent Approval 1/12/2015 432115.2 99   
480X1 No Cancelled Pending Processing 1/7/2014 5060 27.5

Мое текущее решение состоит в том, чтобы использовать курсор и приведенную ниже функцию разделения, чтобы перебрать все отдельные строки, разделить их, повернуть их, а затем вставить в одну из моих динамических временных таблиц. Однако я хотел бы избежать курсора, потому что он дорогой, и предпочтительнее операция на основе набора.

CREATE FUNCTION [dbo].[udf_Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

Есть ли способ добиться того, чего я хочу, без курсора?


sql-server etl sql-server-2012
person Alexus Wong    schedule 03.02.2016    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Отказ от ответственности: я являюсь владельцем проекта Eval SQL.NET

Это решение позволяет разделить и повернуть 100 тыс. строк примерно за 2-3 секунды. Eval SQL.NET позволяет выполнять код C# в T-SQL.

-- CREATE a big string with all the rows (100,000 rows)
DECLARE @s VARCHAR(MAX) = ''
SET @s = ( SELECT TOP ( 100000 )
                    RawSingleLine + CHAR(13) + CHAR(10)
           FROM     Import
FOR XML PATH('') ,
        TYPE
         ).value('text()[1]', 'varchar(max)')

-- Use C# Syntax to split the text. Use Regex.Split if necessary.
DECLARE @sqlnet SQLNET = SQLNET::New('
var rows = s.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
return rows.Select(x => x.Split('' '')).ToList()
').ValueString('s', @s).AutoDispose()

EXEC dbo.SQLNET_EvalResultSet @sqlnet
person Jonathan Magnan    schedule 03.02.2016