SQL Server 2008 - Bulk Insert a whole text file into a single field

Question

I have a text file (txt) containing formatted text (only line breaks, carriage returns and tabs) It also contains German characters.

I want to use the Bulk Insert comment in T-SQL to read in a text file into one field in a database table.

I ran this command:

 CREATE TABLE #MyTestTable (
    MyData NVARCHAR(MAX)
 )

 BULK INSERT [#MyTestTable]
FROM 'D:\MyTextFile.txt'

 SELECT * FROM #MyTestTable

The problem is that it reads each line of the text file into a new line in the Temp table. I want him to read the entire file (formatting and all) in one line.

In addition, German characters look lost - replaced by a non-printable default character in the result view.

Any ideas how I can achieve this?

Thank.

Answer 1

ROWTERMINATOR CODEPAGE. - '\ r\n'. CODEPAGE .

BULK INSERT [#MyTestTable]
FROM 'D:\MyTextFile.txt'
WITH (ROWTERMINATOR = '\0',
      CODEPAGE = 'ACP')

. http://msdn.microsoft.com/en-us/library/ms188365.aspx

Answer 2

:

FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'

| - .

Answer 3

• . . .
• unicode (nvarchar) . , , , .

- - , .