Video Tutorial 6 SQL. INSERT INTO/ INSERT INTO SELECT with MySql Workbench

 

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

SQL INSERT INTO Syntax

To add a new record, you must specify which table the record should go into, which fields you assign values to, and finally the values to be assigned.

It is possible to write the INSERT INTO statement in two forms. The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,...);
    

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

SQL INSERT INTO EXAMPLE

In a new table "publisher2" we insert two new values;

INSERT INTO eli.publisher2 (name, phone) VALUES ('New Store', '555 666');

Below we can see the data in our table after the query SQL INSERT INTO;

---

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

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 INSERT INTO SELECT Example

We are going to copy values from the columns "name" and "phone" from the table "publisher" into the table "publisher2";

INSERT INTO eli.publisher2 (name, phone) SELECT name, phone from eli.publisher;

Below we can see the data in our table after the query INSERT INTO SELECT;

---

Notice that in both the cases of INSERT INTO or INSERT INTO SELECT you don´t have to write the columns if the values match the type and size of the columns we are inserting them into;

INSERT INTO eli.publisher2 VALUES (4, 'Old store', '555 999');

<< Previous

Next >>