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

Question

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

I want to use the Bulk Insert comment in T-SQL to read in the text file into one field within 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 row in the Temp table. I want it to read the whole file (formatting and all) into one row.

Also the German language characters appear to be lost - replaced by a non-printable character default in the Results View.

Anyone any ideas how I can achieve this?

Thanks.

Solution 1

You can use ROWTERMINATOR and CODEPAGE parameters. Default row terminator is '\r\n'. For the CODEPAGE, you need to know encoding of your raw file and default collation of your DB.

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

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

Solution 2

Use this:

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

Where | is your column delimiter.