SQL Bulk Query

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

  • When data is unstructured
  • In case of huge data storage
  • Storing data from a Excel sheet to database
  • Storing hugs amount of data from other resources

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  
  2.   
  3. BULK INSERT BULKQUERY  
  4. -- Path  
  5. FROM 'C:\Users\abhishekj\Desktop\ABC.xls'  
  6. WITH  
  7. (  
  8. FIELDTERMINATOR = ',',  
  9. ROWTERMINATOR = '\n'  

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

  1. -- TRY CATCH  
  2.   
  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)