SQL Server Bulk Insert

Question

I want to import a one column text file into one of my sql tables. The file is just a list of swear words.

I've written the following TSQL to do this

BULK INSERT SwearWords
FROM 'c:\swears.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

However it errors with unexapected end of file. The table im importing to is just an identity field followed by a nvarchar field that I want to insert the text into. It works fine if I add in the text file "1," to the beginning of eveyr line, I assume this is because SQL if looking for 2 fields. Is there any way around this?

Thanks

Answer 1

You need to use FORMATFILE for this. See BULK INSERT.

FORMATFILE [ = 'format_file_path' ]

Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:

* The data file contains greater or fewer columns than the table or view.

* The columns are in a different order.

* The column delimiters vary.

* There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.

For more detailed information, see Using Format Files.