SQL INSERT INTO SELECT Statement


With SQL, you can copy information from one table into another.

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.


The SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected.

SQL INSERT INTO SELECT Syntax

We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

 

SQL Update one table based on another table

Here is one of the most useful SQL commands I know. The example below is a table, Sales_Import, that has incorrect AccountNumber data. So, somebody took the time to put the correct AccountNumber data into a table along with each record’s LeadID.

It is important to note that if the Sales_Import record is NOT in the RetrieveAccountNumber table based on the LeadID, the Sales_Import record has the AccountNumber field populated with null! So, be careful.

UPDATE Sales_Import B
SET AccountNumber =
(SELECT TRIM(AcctNo)
FROM RetrieveAccountNumber A
WHERE B.LeadID=A.LeadID)
WHERE B.State = ‘Michigan’

Dig deeper if you are so inspired.