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 parameter, the whole process should be taken as 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).
Two rows successed, one row failed. It is not taken as a whole transcation process. Please advise. Thansk!
