Массовая вставка MS SQL

У меня есть требование вставить большой файл 2 GB CSV в мою базу данных MS SQL. большую часть строк при этом вставлять не требуется. Я не нашел ничего для фильтрации строк при массовой вставке. Для этого я использую команду массовой вставки MS SQL. Есть ли возможность фильтровать строки на mySQL/MSSQL/Oracle при массовой вставке?

BULK INSERT payroll.t_allowance
   FROM 'f:\orders\lineitem.csv'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      );

sql-server bulkinsert bulk-load
person Zakir Hossain    schedule 31.10.2015    source источник
comment
@suchit Ваше редактирование не удалось. Конечно, это сделало код красивым, но взял английский язык сверху и сильно испортил его. Я не нашел ничего, что можно было бы использовать... А? "Строитель", что это?   -  person zipzit    schedule 31.10.2015
comment
Как насчет массовой вставки, а затем удаления ненужных строк?   -  person BICube    schedule 31.10.2015
comment
Можно ли фильтровать строки перед импортом? Либо скриптовым языком, либо, может быть, type+find?   -  person James Z    schedule 31.10.2015

Ответы (3)


arrow_upward
3
arrow_downward

Вы можете использовать OPENROWSET с опцией BULK:

SELECT *
FROM OPENROWSET(BULK 'f:\orders\lineitem.csv', 
                FORMATFILE= 'f:\orders\format.xml') AS a
WHERE ...

format.xml — это файл, в котором вы настраиваете разделители, имена столбцов, разделители и т. д.: https://msdn.microsoft.com/en-us/library/ms178129.aspx

person Giorgi Nakeuri    schedule 31.10.2015

arrow_upward
3
arrow_downward

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

-- Create a temporary staging table with the same column names and data types, but no indexes
-- Alternatively, use CREATE TABLE
-- When using a permanent table, use TRUNCATE TABLE
SELECT *
INTO #stage
FROM payroll.t_allowance
WHERE 1 = 0;

-- Bulk load the staging table
-- Use the TABLOCK hint to achieve minimally logged inserts
BULK INSERT #stage
FROM 'f:\orders\lineitem.csv'
WITH (TABLOCK, FIELDTERMINATOR = ' |', ROWTERMINATOR = ' |\n');

-- Load the target table from the staging table
INSERT INTO payroll.t_allowance
SELECT s.*
FROM #stage AS s
WHERE...;

-- Drop the staging table
-- or use TRUNCATE TABLE for a permanent table
DROP TABLE #stage;
person Mark Chesney    schedule 23.02.2017

arrow_upward
0
arrow_downward

Использование OPENROWSET(BULK...) - это путь, если вы не можете предварительно отфильтровать файл.

INSERT INTO table1 (col1, col2, ..., colN)
SELECT col1, col2, ..., colN
FROM OPENROWSET(BULK '<data_file_name>.txt', FORMATFILE = '<format_file_name>.xml') AS a
WHERE ...

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

INSERT INTO table1 (col1, col2, ..., colN)
SELECT a.col1, a.col2, ..., a.colN
FROM OPENROWSET(BULK '<data_file_name>.txt', FORMATFILE = '<format_file_name>.xml') AS a
INNER JOIN ...
ON ...
LEFT JOIN ...
ON ...
WHERE ...

См. раздел Импорт массовых данных с помощью BULK INSERT или OPENROWSET(BULK...)

Обратите внимание, что использование OPENROWSET(BULK...) потребует от вас создания файла форматирования в формате XML или в формате, отличном от XML. Оба формата файлов могут быть сложными для написания вручную и легко ошибиться. Ваша работа будет проще, если вы сможете начать с сгенерированного файла и внести необходимые изменения. Сгенерируйте файл формата с помощью программы bcp. Предпочтительнее формат XML (аргумент -x).

bcp <table_or_view> format nul -f<format_file_name>.xml -x

См. раздел Создание файла форматирования.

person Mark Chesney    schedule 23.02.2017