Bulk Insert

Hi all

I need to write a bulk insert statement to import some data from a file, as i have not done this before I used BOL to help. The statement is below:

BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
WITH
(
, DATAFILETYPE = 'char'
, FIELDTERMINATOR = ','
, ROWTERMINATOR = '\n'
)

I have created the .dat file with the data, the problem is when i execute the statement i get this error.

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2, column 1 (pub_id).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 3, column 1 (pub_id).

Any help will be good!! ... Thanks Rich

If you did like I did, you used the Import/Export wizard to create the file. You chose your Sql Server as the Source and a Text file as the destination and specified that you wanted to copy a table.

What I failed to notice was that the table that was selected was the authors table and not the publishers table. So that when I imported it with the command you gave above, I received the exact same errors.

However, when I actually copied out the publisher table, it copied back in without error.

Make sure that you have the data that you expect to have in your file. You should only have 8 records (not 23) and the pub-id column should be only 4 characters long.

|||

Thanks for your reply.

I need to use bulk insert statement as it is part of a sp so the wizard unfortunatly is not the option.

Regards... Rich