What happened to records after BULK INSERT?

Question

SQL Server

Filled the table with data from the file:

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;

Data file encoding UTF-8. All information is in Latin. Problems after executing this code snippet:

1. For some reason, a space was added at the end of the value of each field (I think this is due to ROWTERMINATOR = '0x0a').
   
2. I can not compare with something values ​​in the table in any way. For example, I do I COPY the data from any received cell. I make a request SELECT * FROM Country;

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

   
   

Instead of 'Belgium' any value from copy-paste. As you can see, copied with a space at the end. As a result, the DBMS does not find any matches. I understand if it was Cyrillic. The only option that came to my mind is a problem with the encoding. But I don’t know how to solve it (if it’s still her). Can you help?

Answer 1

UPDATE Country
SET NAME = TRIM(NAME)

Do the same with the temporary table when exporting.