Bulk Query

Sometimes we do faced problems where we need to store a huge amount of data in our database, this causes several serious issues for us as well as for our normal SQL query. So getting rid of all these issues we do use SQL Bulk Queries.

When to Use

How It works

We don't need to do any extra effort in writing SQL Bulk queries, what we simply need to do is add a keyword 'BULK' just before our SQL operation.

Example

In this example am showing simple Insert operation

  1. -- Bulk Query
  3. BULK INSERT BULKQUERY
  4. -- Path
  5. FROM 'C:\Users\abhishekj\Desktop\ABC.xls'
  6. WITH
  7. (
  8. FIELDTERMINATOR = ',',
  9. ROWTERMINATOR = '\n'
  10. )

For more complex queries you can also use functionality of try catch blocks, as

  1. -- TRY CATCH
  3. BEGIN TRANSACTION
  4. BEGIN TRY
  5. BULK INSERT APPTITUDE
  6. FROM 'C:\Users\abhishekj\Desktop\ABC.xls'
  7. WITH
  8. (
  9. FIELDTERMINATOR = ',',
  10. ROWTERMINATOR = '\n',
  11. ROWS_PER_BATCH = 1000,
  12. TABLOCK
  13. )
  14. COMMIT TRANSACTION
  15. END TRY
  16. BEGIN CATCH
  17. ROLLBACK TRANSACTION
  18. END CATCH

(This shows rollback and transaction operation)