Я экспортирую данные программно из Excel в SQL Server 2005 с помощью SqlBulkCopy. Он отлично работает, единственная проблема, с которой я сталкиваюсь, заключается в том, что он не сохраняет последовательность строк, которая у меня есть в файле Excel. У меня нет колонки для заказа, я просто хочу, чтобы записи были вставлены в том же порядке, что и в электронной таблице Excel.
Я не могу изменить файл Excel и работать с тем, что у меня есть. Сортировка по любому из существующих столбцов приведет к разрыву последовательности.
Пожалуйста помоги.
PS Завершена вставка столбца идентификатора в электронную таблицу, похоже, что сохранить экспорт во время экспорта / импорта
Я не думаю, что порядок строк указан или гарантирован SQL, если вы не используете предложение ORDER BY.
Из сообщения Билла Вона ( http://betav.com/blog/billva/2008/08/sql_server_indexing_tips_and_t.html ):
Использование порядка: Даже если таблица имеет кластерный индекс (который хранит данные в физическом порядке), SQL Server не гарантирует, что строки будут возвращены в этом (или каком-либо конкретном) порядке, если не используется предложение ORDER BY.
Еще одна ссылка с информацией:
http://sqlblogcasts.com/blogs/simons/archive/2007/08/21/What-is-the-position-of-a-row--.aspx
После многих исследований кажется очевидным, что нет способа сохранить порядок строк с помощью команды Bulk Insert, написанной, как это показано Microsoft. Вам либо нужно добавить столбец идентификатора непосредственно в файл импорта, либо использовать оболочку или другой внешний скрипт, либо обойтись. Кажется, это была бы необходимая (и простая) функция для Microsoft, чтобы добавить, но после более чем десятилетия ничего от них, это не произойдет.
Тем не менее мне нужно было сохранить фактический порядок записи в файле импорта после импорта, поскольку более высокие записи будут заменять те, которые ниже, если заданный столбец имеет одинаковое значение.
Поэтому я пошел другим путем. Мои ограничения были:
Мне понравилась логика использования Powershell для создания упорядоченных инструкций вставки для каждой строки, а затем для запуска в Sql. Это было по существу очереди каждой записи для отдельной вставки, а не вставки BULK. Да, это сработает, но это будет очень медленно. У меня часто есть файлы с 500K + строками в них. Мне нужно что-то БЫСТРОЕ.
Поэтому я столкнулся с XML. Массовая загрузка файла напрямую в одну переменную XML. Это сохранит порядок записей, поскольку каждый будет добавлен в XML. Затем проанализируйте переменную XML и вставьте результаты в таблицу, добавив столбец идентичности одновременно.
Существует предположение, что файл импорта является стандартным текстовым файлом, причем каждая запись заканчивается линией подачи (Char (13) + Char (10))
Мой подход состоит из двух шагов:
Выполните оператор IMPORT SQL (используя OPENROWSET), инкапсулируя каждую запись с помощью тегов XML. Захват результатов в XML-переменную.
Разберите переменную с помощью тегов 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 секунд.