Как сохранить порядок строк в SqlBulkCopy?

После многих исследований кажется очевидным, что нет способа сохранить порядок строк с помощью команды Bulk Insert, написанной, как это показано Microsoft. Вам либо нужно добавить столбец идентификатора непосредственно в файл импорта, либо использовать оболочку или другой внешний скрипт, либо обойтись. Кажется, это была бы необходимая (и простая) функция для Microsoft, чтобы добавить, но после более чем десятилетия ничего от них, это не произойдет.

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

Поэтому я пошел другим путем. Мои ограничения были:

• Я не мог изменить исходный файл вообще. (и установить плохой прецедент!)
• Я не мог использовать внешний скрипт. Слишком сложно. Это должно быть простое решение на основе T-Sql, без выполнения CMD. Это нужно было сделать в одной процедуре, чтобы ее можно было автоматизировать.

Мне понравилась логика использования Powershell для создания упорядоченных инструкций вставки для каждой строки, а затем для запуска в Sql. Это было по существу очереди каждой записи для отдельной вставки, а не вставки BULK. Да, это сработает, но это будет очень медленно. У меня часто есть файлы с 500K + строками в них. Мне нужно что-то БЫСТРОЕ.

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

Существует предположение, что файл импорта является стандартным текстовым файлом, причем каждая запись заканчивается линией подачи (Char (13) + Char (10))

Мой подход состоит из двух шагов:

1. Выполните оператор IMPORT SQL (используя OPENROWSET), инкапсулируя каждую запись с помощью тегов XML. Захват результатов в XML-переменную.

2. Разберите переменную с помощью тегов XML в таблицу, добавив увеличивающий столбец [ID].

   ---------------------------------
   Declare @X xml;
   ---------------------------------
   SELECT @X=Cast('<X>'+Replace([BulkColumn],Char(13)+Char(10),'</X><X>')+'</X>' as XML)
   FROM OPENROWSET (BULK N'\\FileServer\ImportFolder\ImportFile_20170120.csv',SINGLE_CLOB) T
   ---------------------------------
   SELECT [Record].[X].query('.').value('.','varchar(max)') [Record]
   ,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) [ID]
   --Into #TEMP 
   FROM @X.nodes('X') [Record](X);
   ---------------------------------
   

   • Теги XML заменяют каждый канал.

   • Если файл заканчивается линией подачи, это приведет к добавлению пустой строки в конце. Просто удалите последнюю строку.

Я написал это в своей процедуре, используя динамический sql, чтобы я мог передать имя файла и установить идентификатор для начала в 1 или 0 (если есть строка заголовка).

Я смог запустить этот файл с файлом в 300 тыс. Записей за 5 секунд.