SQL Server Tips & Tricks: Create Bulk Queries

Sometimes you need to build bulk query to select, update or insert data to SQL tables. The selected, updated or inserted data is dynamic and is selected based on data in many tables.

An example of this is the following requirement:

• For each row in a table that belongs to a GUID related to a specific condition in another table, we want to insert another row with specific values.

Note that the number of Insert SQL statements needed can be high based on the initial condition.

To achieve this requirement, there are three options:

• Manually create queries
• Pros: Most efficient if low number of rows
• Cons: Not suitable for large number of rows or complex conditions
• Create a Utility using .Net Framework
• Reads Data from SQL Servers
• Applies commands to add/update/delete data
• Pros: Can be very flexible to apply changes directly or saving script files
• Cons: Time Consuming, Performance is not optimized
• Writing Queries on SQL Server that creates queries for insert/update/delete
• Pros: Simple Syntax, Optimized Performance
• Cons: Not very intuitive when the complexity of the conditions for the query is high.

Generally, each of these methods has its pros and cons so there is no solution that fits all; however, SQL Queries usually gives the best performance with medium complexity.

In my next post, I’ll give some examples on the use of SQL Queries to create queries to insert/update/delete to demonstrate its power in achieving many such scenarios.