Explain Codes LogoExplain Codes Logo

Perform insert for each row taken from a select?

sql
set-based-operations
cursor-based-methods
sql-injection
Alex KataevbyAlex Kataev·Dec 8, 2024
TLDR

Implement bulk inserts using INSERT INTO ... SELECT for transposing rows from one table to another effectively:

-- This isn't a cloning machine, it's an INSERT INTO...SELECT! INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE conditions;

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:

-- It’s SIU time (Select Insert Update)! INSERT INTO investments (portfolio_id, investment_date, amount) SELECT 123 as portfolio_id, GETDATE() as investment_date, amount FROM temp_investments;

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 and SELECT - 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:

-- We're playing string(ing) - join SQL statements, not knots Dim sqlStatement As String sqlStatement = "INSERT INTO table_name (col1, col2) VALUES ('" & cellValue1 & "', '" & cellValue2 & "')"

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:

-- Just entering values in a table, nothing else! INSERT INTO target_table (col1, col2) VALUES ('value1', 'value2'), ('value3', 'value4'), ...;

MERGE to the Rescue

MERGE = INSERT + UPDATE + DELETE all in one. Here's one tool to rule them all!