SQL Server Bulk Insert

Question

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

I wrote the following TSQL to do this

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

However, these are errors with an unused end of file. Importing an im table is just an identifier field, followed by an nvarchar field into which I want to insert text. It works fine if I add "1" to the text file at the beginning of the eveyr line, I assume that this is because SQL is looking for 2 fields. Is there any way around this?

thanks

Answer 1

For this you need to use FORMATFILE. See BULK INSERT .

FORMATFILE [= 'format_file_path']

Specifies the full path of the file format. The format file describes the data file containing the stored responses created using the bcp utility on the same table or view. The format file should be used when:

 * 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. 

See Using format files for more information.

Answer 2

This is described in line-by-line books for BULK INSERT in the KEEPIDENTITY argument. That's what it says

KEEPIDENTITY Indicates that the values ​​for the identity column are present in the imported file. If KEEPIDENTITY is not specified, the identifier values ​​for this column in the imported data file are ignored, and SQL Server automatically assigns unique values ​​based on the seed and increment values ​​specified when creating the table. If the data file does not contain values ​​for the identity column in the table or view, use the format file to indicate that the identifier column in the table or view should be omitted when importing data; SQL Server automatically assigns unique values ​​to a column

So, use a format file or put a dummy value and make sure not to use the KEEPIDENTITY argument

Answer 3

Alternatively, you can create a view in your table based on only the nvarchar column, and then BULK INSERT into your view. This is a very clean way to use BULK INSERT.

This way you don't have to worry about the IDENTITY column or creating a format file.

Your BULK INSERT statement should look like this:

 BULK INSERT vw_SwearWords FROM 'c:\swearwords.txt' WITH (ROWTERMINATOR = '\n') 

Answer 4

You need to make sure that the structure of your text file and the table matches - if the table has two fields, then you also need to specify two fields / columns in the text file.

Since the first column in the SQL table is an IDENTITY field, you can provide any value you want, but you should have a value there, I don't think it is possible.

Mark

Answer 5

Check that the last line has CR / LF (\ r \ n). Sometimes this is a problem, and sometimes another carriage return is at the end of the file. You can check this with hexeditor.

Answer 6

You can remove the identifier column and return it when done. Alternatively, if this violates your relationship with the database, you can import using DTS or SSIS, if it was once a disabled import - more detailed control is messing with the columns

Answer 7

In SQL Server 2008, it was easier for me to simply create a file containing many attachments, for example, and paste it into the Management Studio query window, instead of struggling with getting a format file and bulk inserting permissions files

 USE YourDB GO INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('First',1), ('Second',2), ('Third',3), ('Fourth',4), ('Fifth',5) 

http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/