Learn How to Use INSERT INTO SELECT Statement in SQL

Learn about using INSERT INTO SELECT statement. In SQL we can copy the data rows from one table to another table. The INSERT INTO SELECT statement copies the data rows from one table and inserts into an existing table. Both actions are executed in a single SQL statement. The source table and target table must have similar table definition that is the same columns with same data types. Any existing data rows or records in target table remain unaffected.

Here are the examples showing use of INSERT INTO SELECT statements,
"INSERT INTO targetTable SELECT * FROM sourceTable;"
"INSERT INTO targetTable (column1, column2) SELECT column1, column2 FROM sourceTable;"
We can copy all columns or specified columns from source table to target table.

Select the "school" database, "USE school;"
First, lets create a new students table which will act as as target table to store the data rows.
"CREATE TABLE students2 ( studentid INT PRIMARY KEY AUTO_INCREMENT, firstname VARCHAR(40) NOT NULL, lastname VARCHAR(40) NOT NULL, class VARCHAR(20), age INT );"
Hit enter to create the table. Lets populate the 'students2' table from source table 'students' using INSERT INTO SELECT statement.
The SQL statement will be, "INSERT INTO students2 SELECT * FROM students;"
Lets get the data rows from 'students2' table, "SELECT * FROM students2;"
And we have data rows in our new 'students2' table.

Check out the whole playlist of SQL Tutorials or Individual video from the following links,

SQL Tutorial for Beginners. Introduction to SQL Basics

1.Learn What is SQL? Introduction to SQL

2.Learn How to Install MySQL Database Server on Windows Operating System

3.Learn What is Database? How to Create and Show Databases using SQL

4.Learn How to Select and Use Existing Database using SQL

5.Learn How to Delete or Remove Database using SQL

6.Learn What is Database Table? How to Create a Database Table using SQL?

7.Learn How to Modify or Update Database Table using SQL

8.Learn How to Delete or Remove Database Table using SQL

9.Learn How to Add Data Rows in a Table using SQL

10.Learn How to Use INSERT INTO SELECT Statement in SQL

11.Learn How to Update Data Rows using Update Statement in SQL

12.Learn How to Delete Data Rows using Delete Statement in SQL

[jrlopes]

Thanks, saved me today, helped me migrate a Firebird procedure to MySQL

[rexfarell]

0:55 practical examples
1:03 creating the target table
3:09 copying from the original table to the target table
4:21 inserting only selected columns

[ibell]

thanks, how about copying timestamp datatype?

[anujgupta]

what will happen if we insert the data in the same table from which we are taking?