Что произошло с записями после BULK INSERT?

Question

[CodeInside]

Заполнил таблицу данными с файла:

CREATE TABLE Country (
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name NVARCHAR(64) NOT NULL UNIQUE
	);
GO
CREATE TABLE #CountriesFromFile (Name NVARCHAR(MAX));

BULK INSERT #CountriesFromFile
FROM 'E:\MS-SQL\Exam\Data\Countries.txt'
	WITH (
		ROWTERMINATOR = '0x0a'
		);

INSERT INTO Country
SELECT *
FROM #CountriesFromFile;

DROP TABLE #CountriesFromFile;

Кодировка файла с данными UTF-8. Все данные на латинице. Проблемы после выполнения данного фрагмента кода:

1. Почему-то в конце значения каждого поля добавился пробел (думаю это связано с ROWTERMINATOR = '0x0a').
   
2. Никак не могу сравнивать с чем-то значения в таблице. Например, делаю SELECT * FROM Country; КОПИРУЮ данные с какий-либо полученной ячейки. Делаю запрос

   SELECT *
   FROM Country
   WHERE Country.[Name] = 'Belgium ';

   
   

Вместо 'Belgium ' какое-либо значение с копипаста. Как видите, скопировало с пробелом в конце. В итоге СУБД не находит никаких совпадений. Понимаю если бы это была кириллица. Единственный вариант, который мне пришёл в голову - проблема с кодировкой. Но не знаю как её решить (если это ещё она). Поможете?

Answer 1

[Константин Цветков]

UPDATE Country
SET NAME = TRIM(NAME)

Тоже проделывать с временной таблицей при экспорте.

Comments

[CodeInside]

Функции TRIM нету в моей СУБД.
Выполнил:

UPDATE Country
SET NAME = RTRIM(NAME);
UPDATE Country
SET NAME = LTRIM(NAME);

В конце каждой записи добавило '\r\n'. Зато находит записи. Спасибо большое)

[Константин Цветков]

Функции TRIM нету в моей СУБД.

Да, не учёл.

SQL Server (начиная с версии 2017)

[CodeInside]

У меня 2016 :(

Answer 2

[d-stream]

Глядя на bulk insert во временную таблицу я немного взгрустнул)))

Рекомендую посмотреть на "инверсный" вариант:

select
or.* -- или явный перечень того что надо с нужными трансформациями типа ltrim(rtrim()), cast() итп
from openrowset (
BULK 'E:\MS-SQL\Exam\Data\Countries.txt', 
FORMATFILE =  'E:\MS-SQL\Exam\Data\Countries.xml'
) as or
-- а тут можно нужные join, where, group итп.

Придется чутка подразобраться со структурой форматфайла, но это принесет неоценимую пользу в плане более тщательного указания что за данные должны быть, перекодировок и т.п. По сути получится карманный вариант import-export wizard

Comments

[CodeInside]

Что-то не сильно получается

INSERT INTO Country
SELECT *
FROM OPENROWSET
	(	BULK 'E:\MS-SQL\Exam\Data\Countries.txt',
		FORMATFILE = 'E:\MS-SQL\Exam\fmt\Countries.fmt'
	) AS CountryName;

Ошибка: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (Id).

Я так понял, СУБД ожидает, что я и Id передам? Я не особо понял что писать после

FORMATFILE = 'E:\MS-SQL\Exam\fmt\Countries.fmt' ) -- вот здесь

Что-то в документации не могу разобраться(

[CodeInside]

При этом Countries.fmt:

11.0
1
1 SQLCHAR 0 100 "\r\n" 1 CountryName ""

[d-stream]

CodeInside, лучше все-таки более современный .xml:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="какой-нить ID" xsi:type="NativeFixed" LENGTH="19" />
  <FIELD ID="Другой ID" xsi:type="NativeFixed" LENGTH="15" />
<FIELD ID="последняя - терминатор строки" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20" COLLATION="Cyrillic_General_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="какой-нить ID"  NAME="как хочется обзывать поле в выхолпе" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="Другой ID"  NAME="Название второй колонки" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

То есть собственно две секции:
1. описание колонок в исходном материале (позиционные, разделитель и т.п.)
2. описание что выводить в конце (source ссылается на первую секцию)

К примеру в варианте дурного вражьего текстового формата у меня в первой секции было в раза три больше строк - там и позиционность и всякие разделители-заполнители

[d-stream]

CodeInside, ну а гуглить - по "xml bcpformat"