Insert into mysql untuk apa?

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');

The article covers the basic syntax of the MySQL INSERT statements and explains how to use the INSERT command in the MySQL table using dbForge Studio for MySQL.

Introduction to a MySQL INSERT Statement

The INSERT statement is used to add data to a table. The INSERT INTO command inserts one or multiple rows into a MySQL table. Depending on the number of rows you want to add, the syntax slightly differs.

MySQL INSERT INTO – General Syntax

When inserting a single row into the MySQL table, the syntax is as follows:

INSERT INTO table_name(column_1,column_2,column_3) 
VALUES (value_1,value_2,value_3); 

In the INSERT INTO query, you should specify the following information:

• table_name: A MySQL table to which you want to add a new row.
• (column_1,column_2,column_3): A list of columns the new row will contain. The columns are separated by a comma in round brackets.
• (value_1,value_2,value_3): A list of values separated by a comma in round brackets that will be used for each corresponding column.

The number of columns and values should match; otherwise, it fails.

When inserting multiple rows into a MySQL table, the syntax is as follows:

INSERT INTO table_name (column_1, column_2, column_3, ...) 
VALUES 
      (value_list_1), 
      (value_list_2), 
      ...
      (value_list_n); 

Here you should enter the following information:

• table_name: A table into which you want to insert data.
• column_1, column_2, column_3: A list of columns to store data. The columns are separated by a comma in round brackets.
• value_list_1/2/.../n: A list of values to be inserted in one row. The values are separated by a comma in round brackets. The number of values in each list should match the number of columns specified in (column_1, column_2, column_3, ...).

In addition, rows are separated by commas in the VALUES clause.

MySQL INSERT Examples

Let’s explore some examples of using a MySQL INSERT statement when performing data-related tasks.

To start with, create three tables: Customers, Products01, and Smartphones by executing the following script:

CREATE TABLE Customers
(
  ID int,
  Age int,
  FirstName varchar(20),
  LastName varchar(20)
);

-- Creating the Products table

CREATE TABLE Products01
(
  ID int,
  ProductName varchar(30) NOT NULL,
  Manufacturer varchar(20) NOT NULL,
  Price decimal NOT NULL,
  ProductCount int DEFAULT 0
);

CREATE TABLE Smartphones
(
  ID int,
  ProductName varchar(30) NOT NULL,
  Manufacturer varchar(20) NOT NULL,
  Price decimal NOT NULL,
  ProductCount int DEFAULT 0
);

MySQL INSERT command used to insert specified values

This command can be used when it is necessary to add values and columns for the specified rows. For example, add data only for the FirstName and LastName columns of the Customers table.

INSERT INTO Customers(ID, FirstName, LastName)
VALUES ('1', 'User', 'Test');

The command inserts values in the FirstName and LastName columns of the Customers MySQL table. Since the Age column uses Null as a default value, MySQL inserts Null into the Age column if you do not specify explicitly its value in the INSERT statement.

MySQL INSERT command used to insert values to all columns

This command can be used in order to add values to all columns. For example, add values to the Product01 table. Keep in mind that the order of values should match the order of columns in the table.

INSERT INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

The statement inserts the specified values into all columns of the Product01 table.

MySQL INSERT command used to insert the default value

This command can be used when the default value is specified for the column. For example, the default value for the ProductCount column of the Products01 table equals 0. To insert the default value, execute either of the following queries:

INSERT INTO Products01(ProductName, Manufacturer, Price, ProductCount)
VALUES ('Nokia 9', 'HD Global', '41000', DEFAULT);

INSERT INTO Products01 (ProductName, Manufacturer, Price, ProductCount)
VALUES ('Nokia 9', 'HD Global', '41000', NULL);

The statement inserts the default value for the ProductCount column.

MySQL INSERT used to insert a specific value

To insert values into the columns, we can use the SET clause instead of the VALUES clause.

INSERT INTO Customers SET ID=2, FirstName='User2';

In the output, the statement inserts values into the columns based on the explicitly specified values in the SET clause.

MySQL INSERT used to insert data from another table

Using the SELECT clause in the MySQL INSERT INTO statement allows inserting the rows generated by the SELECT statement into the target MySQL table.

INSERT INTO Products01 SELECT * FROM Smartphones;

In this case, the INSERT INTO statement copies the rows you retrieved with the SELECT statement from the Smartphones table and inserts them into the Products01 table.

If you want to retrieve specific rows based on the condition, apply filtering options in the WHERE clause. Keep in mind that the structure of both tables should match; otherwise, it won’t work.

INSERT INTO Products01 SELECT * FROM Smartphones WHERE Price=34000;

This command adds to the Products01 table values from the Smartphones table where the price equals 34000.

You can also copy and insert specified columns from another table. However, in this case, make sure that there is no record with the specified name in the target table; otherwise, it fails, and the error occurs.

MySQL INSERT – ignore errors when inserting data

In case you face errors or any unexpected behavior while executing the insertion of rows into the MySQL table, the statement execution is interrupted. Rows with valid and invalid values are not added to the table. For example, execute the following query:

INSERT INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

This statement returns the error because the record with this ID already exists. Now, modify the statement with IGNORE. It allows you to insert rows with valid values but overpass the rows with invalid values.

INSERT IGNORE INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

As a result, the script was executed without errors. MySQL will notify you that rows were added to the table.

Creating the INSERT INTO Query with dbForge Studio for MySQL

All the data manipulations, including the Create, Select, Insert, Update, Delete, or Drop operations can be easily performed with dbForge Studio for MySQL. The tool offers a rich set of advanced capabilities and features to boost your productivity and save your time when developing, managing, and deploying MySQL database in the easy-to-use interface.

Assume we need to add a new customer to the database. In dbForge Studio for MySQL, we can use the Generate Script As feature to insert one or multiple rows in the MySQL table.

To insert information about customers, do the following:

1. In Database Explorer, right-click the required table and select Generate Script As > INSERT > To New SQL Window. The SQL code editor opens containing the automatically generated script.

2. In the SQL code editor, insert data for the specified columns (first_name, last_name, and email) into the VALUES clause.

3. On the standard toolbar, click Execute.The statement adds the First Name, Last Name, and email of the new customer to the bottom line of the grid.

Conclusion

In the article, we have reviewed a MySQL INSERT statement and examples, when the command can be used, and provided a particular example of how to work with the MySQL INSERT queries in dbForge Studio for MySQL.

Download a free 30-day trial version of dbForge Studio for MySQL to evaluate all the useful features the tool provides.

• Author
• Recent Posts

mysql insert statement, mysql tools

Apa yang dimaksud dengan insert into?

Apa fungsi dari insert pada database?

Apa itu query di MySQL?

Bagaimana sintaks Insert pada SQL?