Perform insert for each row taken from a select?
Implement bulk inserts using INSERT INTO ... SELECT for transposing rows from one table to another effectively:
Note, target columns must coincide with the source data. Also, introducing fixed values likeportfolio_id
is a breeze - simply pin them in your SELECT
clause:
Just remember portfolio_id
is now a constant; GETDATE() gets the timestamp, and NULL is a good placeholder for a field such as last_modified_date
if not applicable initially.
Set-based Operations versus Cursors
Efficiency of Sets over Cursors
In the land of SQL, set-based operations rule. Why? They are fabulously faster than cursor-based methods that work line by line, making them especially efficient for large datasets:
- Sets: Transfers multiple rows in one hit! 🎯
- Cursors: A single-row approach that cries 'I miss BASIC!' 🐢
Performance Enhancement Hyperspeed
- Match column order between
INSERT
andSELECT
- it's like arranging socks by color. - Discard unnecessary columns in
SELECT
list to lighten the load. - Ever tried running with weights? That's tables with constraints or indexes during insertion. Remove them ahead, but remember to put them back on!
Excel VBA: Rapid Automation
Generating SQL Effortlessly
Excel VBA can help you create SQL statements at the speed of thought:
Safety first: Sterilize inputs and beware the big bad wolf of SQL injection.
When to Use VBA
- Unleash VBA when you need to evacuate data from Excel to SQL 🚁
- Need SQL for once-off ETL? VBA to the rescue!
Alternative techniques to Cursors
The Bulk Brigade
Consider BULK INSERT, bcp or OPENROWSET to transport large data if your source is a flat file.
Table-Valued Constructors
When you want to insert multiple rows without a long SELECT
statement, table-valued constructors come in handy:
MERGE
to the Rescue
MERGE
= INSERT
+ UPDATE
+ DELETE
all in one. Here's one tool to rule them all!
Was this article helpful?