Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server > SQL Server Forums > SQL Server Database Engine > Insert error record in Database table using bulk insert

Answered Insert error record in Database table using bulk insert

    •  
      Tuesday, May 01, 2012 12:58 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Hello all,

      I am using a BULK Insert command in sql as shown below:

      EXEC ( 'BULK INSERT TMP_SAE FROM ''' + 'C:\Users\Administrator\Desktop\Stored procedure\extract2.csv' + ''' WITH 
      ( FIRSTROW = 2,
      MAXERRORS =50000,
      FIELDTERMINATOR = ''|'' ,
      ROWTERMINATOR   =''\n'',
      ERRORFILE = ''C:\Users\Administrator\Desktop\Stored procedure\ERROR.txt''
      )'
      )

      Now when there is an error in the csv file the it creates two text files (i.e Error.txt and Error.txt.Error), and it does not write that row in the database table

      Now what i want is that instead of writing the error log to the text file I should insert that error message in the database table.

      Please help me around this issue.

      Thanks in advance.

      Pankaj Kumar Yadav-


       

    Answers

    • Tuesday, May 01, 2012 3:31 PM
       
       Answered
      Sign In to Vote
      1
      Sign In to Vote

      Hi Pankaj,

      unfortunately, this is a limitation of the BULK INSERT command. You cannot directly log error rows into a database table using BULK INSERT. This is because the BULK INSER command validates the data in your file against the specified table|view that exists in your target database. When the validation fails for any reason (data type or number or order mismatch), the BULK INSERT just discards that row and logs it into the error file. You can always load that error file into a log table (that is suitable designed, but you cannot always count on the error file to be in a particular format - it depends on the data) at a later time .

      This is all microsoft provides with the BULK INSERT. The rich functionality that you need can be achieved by using SSIS packages for file load, since they let you log error records into a database table.

      hope this helps!


      Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com