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
- -- Bulk Query
-
- BULK INSERT BULKQUERY
- -- Path
- FROM 'C:\Users\abhishekj\Desktop\ABC.xls'
- WITH
- (
- FIELDTERMINATOR = ',',
- ROWTERMINATOR = '\n'
- )
For more complex queries you can also use functionality of try catch blocks, as
-
-
- BEGIN TRANSACTION
- BEGIN TRY
- BULK INSERT APPTITUDE
- FROM 'C:\Users\abhishekj\Desktop\ABC.xls'
- WITH
- (
- FIELDTERMINATOR = ',',
- ROWTERMINATOR = '\n',
- ROWS_PER_BATCH = 1000,
- TABLOCK
- )
- COMMIT TRANSACTION
- END TRY
- BEGIN CATCH
- ROLLBACK TRANSACTION
- END CATCH
(This shows rollback and transaction operation)