MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table
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.
MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table