<p>If I run the following query in SQL Server 2000 Query Analyzer:</p>

<pre><code>BULK INSERT  OurTable 
FROM 'c:\OurTable.txt' 
WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', ROWS_PER_BATCH = 10000, TABLOCK)
</code></pre>

<p>On a text file that conforms to OurTable's schema for 40 lines, but then changes format for the last 20 lines (lets say the last 20 lines have fewer fields), I receive an error. However, the first 40 lines are committed to the table. Is there something about the way I'm calling Bulk Insert that makes it not be transactional, or do I need to do something explicit to force it to rollback on failure?</p>

[{'answer_id': 41942, 'author': 'kaiz.net', 'author_id': 3714, 'author_profile': 'https://Stackoverflow.com/users/3714', 'pm_score': 0, 'selected': False, 'text': '<p>Try to put it inside user-defined transaction and see what happens. Actually it should roll-back as you described it.</p>\n'}, {'answer_id': 152695, 'author': 'Josef', 'author_id': 5581, 'author_profile': 'https://Stackoverflow.com/users/5581', 'pm_score': 6, 'selected': True, 'text': "<p><code>BULK INSERT</code> acts as a series of individual <code>INSERT</code> statements and thus, if the job fails, it doesn't roll back all of the committed inserts.</p>\n\n<p>It can, however, be placed within a transaction so you could do something like this:</p>\n\n<pre><code>BEGIN TRANSACTION\nBEGIN TRY\nBULK INSERT  OurTable \nFROM 'c:\\OurTable.txt' \nWITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\\t', \n   ROWS_PER_BATCH = 10000, TABLOCK)\nCOMMIT TRANSACTION\nEND TRY\nBEGIN CATCH\nROLLBACK TRANSACTION\nEND CATCH\n</code></pre>\n"}, {'answer_id': 9520696, 'author': 'Guillermo Garcia', 'author_id': 247684, 'author_profile': 'https://Stackoverflow.com/users/247684', 'pm_score': 1, 'selected': False, 'text': '<p>As stated in the <code>BATCHSIZE</code> definition for BULK INSERT in MSDN Library (<a href="http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx</a>) :</p>\n\n<p>"If this fails, SQL Server commits or rolls back the transaction for every batch..."</p>\n\n<p>In conclusion is not necessary to add transactionality to Bulk Insert.</p>\n'}, {'answer_id': 41284802, 'author': 'Sai Bhasker Raju', 'author_id': 2431980, 'author_profile': 'https://Stackoverflow.com/users/2431980', 'pm_score': 2, 'selected': False, 'text': "<p>You can rollback the inserts . To do that we need to understand two things first</p>\n\n<blockquote>\n  <p><code>BatchSize</code></p>\n  \n  <p>: No of rows to be inserted per transaction . The Default is entire\n  Data File. So a data file is in transaction</p>\n</blockquote>\n\n<p>Say you have a text file which has 10 rows and row 8 and Row 7 has some invalid details . When you Bulk Insert the file without specifying or with specifying batch Size , 8 out of 10 get inserted into the table. The Invalid Row's i.e. 8th and 7th gets failed and doesn't get inserted.</p>\n\n<p>This Happens because the Default <code>MAXERRORS</code> count is 10 per transaction.</p>\n\n<p><strong>As Per MSDN :</strong></p>\n\n<blockquote>\n  <p>MAXERRORS :</p>\n  \n  <p>Specifies the maximum number of syntax errors allowed in the data\n  before the bulk-import operation is canceled. Each row that cannot be\n  imported by the bulk-import operation is ignored and counted as one\n  error. If max_errors is not specified, the default is 10.</p>\n</blockquote>\n\n<p>So Inorder to fail all the 10 rows even if one is invalid we need to set <code>MAXERRORS=1</code> and <code>BatchSize=1</code> Here the number of BatchSize also matters. </p>\n\n<p>If you specify BatchSize and the invalid row is inside the particular batch , it will rollback the particular batch only ,not the entire data set.\nSo be careful while choosing this option  </p>\n\n<p>Hope this solves the issue.</p>\n"}]
['https://Stackoverflow.com/questions/41869', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2831/']