MySQL Insert

 

The INSERT command inserts one or more rows into a table. Let’s see how the syntax works.

INSERT INTO table_name(column_name1,column_name2,..)
VALUES (column_value1,column_value2,...);
INSERT INTO `employee` 
(`age`, `email`, `first_name`, `last_name`, `title`) 
VALUES 
('20', 'test@gmail.com', 'John', 'Brown', 'Manager');

INSERT INTO table_name – this query is an insert query into a table.
VALUES – values for the columns you have chosen.
* The number of columns and values must be equal otherwise it won’t work.


* It is very important that you insert string data within single quotes to avoid abnormal behavior.
For example if your string data has a comma in it, mysql will complain as it thinks you are inserting into more columns than specified. 

-- it seems like we are inserting data into 3 columns which is not true
INSERT INTO `employee` 
(`first_name`, `last_name`) 
VALUES 
(John,roe, Brown);

-- using single quotes
INSERT INTO `employee` 
(`first_name`, `last_name`) 
VALUES 
('John,roe', 'Brown');

 


You can also add multiple rows in a single insert call.

Insert with multiple rows in a single call

INSERT INTO table_name(column_name1,column_name2,..)
VALUES 
(column_value1,column_value2,...),
(column_value1,column_value2,...),
(column_value1,column_value2,...);
INSERT INTO `employee` 
(`age`, `email`, `first_name`, `last_name`, `title`) 
VALUES 
('20', 'test@gmail.com', 'John', 'Brown', 'Manager'), 
('35', 'test1@gmail.com', 'James', 'Brown', 'Manager');

Insert into a table what is selected from another table

Let’s say you have a table and for whatever reason, you want to copy it to another table. What you would do is, create the new table and then move the data from the old table to the new table.

INSERT INTO table_name1(column_name1,column_name2,...)
/* replace VALUES with this */
(SELECT 
   column_name1,column_name2,...
FROM 
   table_name2);

VALUES is replaced by a SELECT statement which reads the data from table_name2.

INSERT INTO `customer`(`first_name`,`last_name`)

(SELECT `first_name`,`last_name` 
FROM `store`.`employee`);

As you can see, we just inserted all first names and last names from our employee table into the first_name and last_name columns for the customer table.

Insert with ignore

When we are inserting multiple rows into a table and something goes wrong, mysql will roll back all transactions so none of the rows are added. If we want to ignore any error and continue the sql execution then we have to use IGNORE. IGNORE indicates that if any of the row fails that other rows that are good should be inserted.

INSERT IGNORE INTO table_name(column_name1,column_name2,..)
VALUES 
(column_value1,column_value2,...),
(column_value1,column_value2,...),
(column_value1,column_value2,...);
INSERT IGNORE INTO `customer`
(`first_name`,`address_id`)
VALUES
('James',20),
('Peter','Rollin'),
('John',3);

As you can see, James was added but Peter Rollin was not added because ‘Rollin’ was an invalid value for that column. The SQL execution did not stop and roll back instead it continued to add John.

 




Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


Leave a Reply

Your email address will not be published.