Insert with SELECT

Transforming data while transferring

INSERT INTO...SELECT allows you to revamp data in-flight. Use SQL functions or case statements to alter column values during the transfer.

INSERT INTO destination_table (col1, transformed_col2)
SELECT col1, CASE WHEN col2 > 10 THEN 'High' ELSE 'Low' END FROM source_table;


Extra points for style if you transform columns on the go! 🎩

Handling schema mismatches

INSERT INTO...SELECT can amend mismatched schemas by mapping source data to target columns. Use constant values for columns absent in the source but present in the target.

INSERT INTO destination_table (col1, col2, static_col)
SELECT a.col1, b.col2, 'Static' FROM table1 a JOIN table2 b ON a.id = b.id;


Who said you couldn't make bricks without clay? Static values save the day. 🦸

Protecting your state

It's not paranoia if they're really out to get your data. Back up before carrying out gargantuan INSERT INTO...SELECT operations.

-- Backup: Call in the stunt double!
SELECT * INTO backup_table FROM destination_table;


Trust me, having a stunt-double for your data can save your gigabytes.

Tackling differing column types

Type mismatch between source and destination can be a hurdle. Use CAST() or CONVERT() to coordinate the datatypes within the SELECT statement:

INSERT INTO destination_table (int_col, date_col)
SELECT CAST(char_col AS INT), CONVERT(date, char_date, 101) FROM source_table;


Loading fixed values

INSERT INTO...SELECT also entertains constant values. Just insert them in the SELECT statement for the desired column:

INSERT INTO destination_table (data_col, gid)
SELECT data_col, 'MY_GID' FROM source_table;


Who said you couldn't add some dramatic personality during data transfers? Be a diva, set your own GID.

The volcanic subqueries

Unleash the hidden power of subqueries within the SELECT statement for complex value operations:

INSERT INTO destination_table (price_sum)
SELECT (SELECT SUM(price) FROM source_table WHERE product = 'banana') FROM dual;


Shout out to all those bananas that made this transaction possible! 🍌

How it works?

Column specific insertion

The beauty of the INSERT INTO… SELECT method permits selective data transfer only to the required columns, demonstrated below:

INSERT INTO new_table (col1, col3)
SELECT col1, col2 FROM current_table;


This operation fills the col3 in new_table with col2 from current_table. Et voila! Data successfully rearranged.

Filtering data

A WHERE clause in the SELECT statement lets you control the data that is duplicated. This clause refines the transferred data:

INSERT INTO new_table (col)
SELECT col FROM current_table WHERE col != 'Error404';


No more "Page not found" errors!

Syntax checkup

Don't neglect to cross-verify your syntax. This helps avoid type mismatches or constraint violations.