BULK INSERT question

• DBA328

  Hall of Fame

  Points: 3952

  More actions

  September 20, 2012 at 7:41 pm

  #262030

  I have a question about whether BULK INSERT handle a file as a transcation, from MSDN i found this:

  BATCHSIZE =batch_size

  Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch. For information about performance considerations, see "Remarks," later in this topic.

  http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx

  It seems if we not indicate BATCHSIZE, the whole process should be a transcation, if one row failed, then the whole process should roll back.

  Then i did a testing:

  text file data:

  "John","Smith","bill@smith.com"

  "Sara","Parker","sara@parker.com"

  "Kelly","Reynold","kelly@reynold.com"

  create a table:

  CREATE TABLE [dbo].[Person](

  [FirstNmae] [varchar](50) NULL,

  [LastName] [varchar](50) NULL,

  [EmailAddress] [varchar](16) NULL

  ) ON [PRIMARY]

  use bulk insert deal with data:

  BULK INSERT Person FROM 'D:\test.txt' WITH (FIELDTERMINATOR = '","')

  BULK INSERT Person FROM 'D:\test.txt' WITH (FIELDTERMINATOR = '","',BATCHSIZE =10000)

  error:

  Msg 4863, Level 16, State 1, Line 2

  Bulk load data conversion error (truncation) for row 3, column 3 (EmailAddress).

  At last, two rows are inserted, and it didn't take the whole process as transcation. so i am confused. Thanks!