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

-- 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

-- TRY CATCH

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)