Unlike Data Definition statements, DML statements are not auto-commit transactions by default. Hence, until we specifically \u201ccommit\u201d the transaction, the changes can be undone. These commands also correspond to the \u201cCRUD\u201d (Create-retrieve-update-delete) statements which most front-end applications use.<\/p>\n
2.1 What is a Transaction?<\/h3>\n
A transaction is a unit of work, performed in the order in a database. Transactions propagate or reverse one or more changes done to a database. Transactions are used for DML statements and are extremely important to ensure data integrity. More on transactions in general here<\/a>.<\/p>\n In general, the Insert statement has 2 syntaxes. The first syntax is to insert only specific columns in a table.<\/p>\n For this insert statement syntax, all the columns with the \u201cNot NULL\u201d constraint must be inserted. The columns which are not inserted, get the value of \u201cNULL\u201d. Alternatively, we can also specify a default value to be specified by using the DEFAULT Keyword.<\/p>\n If all the columns in the table are to be filled, we can simply skip the column_names and directly give the values.<\/p>\n The syntaxes above will insert a single record in the table mentioned. To insert multiple records into a table, depending on the RDBMS we use, different syntaxes are available. For example, to insert multiple records at once in Oracle, we use the \u201cINSERT ALL\u201d while the MySQL and PostgreSQL use the \u201cINSERT INTO\u201d. Microsoft SQL does not support multiple record insertion<\/p>\n The full syntax of the insert query as per the MySQL docs is<\/p>\n To understand the most used options and their syntaxes, we will look at some examples next.<\/p>\n Forgoing through the examples related to Insert, we will consider a new database called \u201cTestDB\u201d. This database has 3 tables named employees, departments, and employee_dept.<\/p>\n The Create scripts for the 3 tables are as follows:<\/p>\n We also have another table called EMPLOYEES2 which has the same structure as the Employees table. For running the queries, we will use the MySQL Workbench. The documentation for MySQL Workbench is available here<\/a>. Set the Workbench to \u201cAuto-commit Transactions\u201d.<\/p>\n Using the setup above we will look at various options and syntaxes used with the Insert statements.<\/p>\n To insert a single row in say the Employees table, we use the query<\/p>\n The output is as follows:<\/p>\n If you observe, the first column in the employees <\/em>table: EMP_ID is not mentioned in our Insertion Query but is still inserted with a correct value. The reason for that is we have declared the EMP_ID as an auto_increment value. It means that MySQL generates a sequential integer whenever a row is inserted into the table.<\/p>\n If all the columns are to be inserted in a table, we can skip the column names altogether and just put the values. While doing so, we must set the EMP_ID i.e. the auto_incremented value to a DEFAULT value.<\/p>\n The output is as follows:<\/p>\n In the employees <\/em>table, we have defined the Birth_date with the default value as \u201c1900-00-00\u201d. When during insertion we do not mention the birth_date, then the record is inserted with the default value.<\/p>\n The output is as follows:<\/p>\n If a column is specified as \u201cNot NULL\u201d, it necessarily needs to have a value inserted in it. If we try to insert a record without a value specified for the \u201cNot NULL\u201d column, then insertion fails and the record is not inserted.<\/p>\n The output is as follows:<\/p>\n To insert multiple records in the Departments table, we use the query<\/p>\n Here again, the DEPT_ID column is auto-increment and will be generated in Sequence.<\/p>\n For Insertion in a table, with references to other tables i.e. foreign keys, the records for the Foreign key must exist in the other table before we can insert into the current table.<\/p>\n Correct Foreign key reference query<\/p>\n The output is as follows:<\/p>\n Here both the records for the EMP_ID and DEPT_ID exist in their respective tables.<\/p>\n IF the Foreign key we are trying to insert is either not present or is incorrect, then record cannot be inserted<\/p>\n The output is as follows:<\/p>\n Here the record for the DEPT_ID used is not present in the Departments table.<\/p>\n If we have two identical tables or even one table which has a subset of columns from the other table, we can use the select query with the insert query.<\/p>\n To select a particular record to insert<\/p>\n If the Columns names in the two tables are identical then we can skip the column names in the Select query and use the asterisk (*) instead.<\/p>\n The queries above give the output:<\/p>\n To insert all the records from one table to another , we can use any one of the syntaxes of the select statement from above, just without a where clause<\/p>\n The output is:<\/p>\n If we try to insert a row in a table with a primary key that already exists, we get a \u201cDuplicate entry\u201d error. This would apply to a column that has a \u201cUnique\u201d constraint as well.<\/p>\n The error shown is<\/p>\n However, if we use the On DUPLICATE KEY UPDATE statement with the insertion, then the record already present in the table is updated instead. Example Query<\/p>\n The Emp_id = 4 already exists in the Employees2 table. When we mentioned, the on Duplicate key update statement, the record for the emp_id=4 is retrieved and updated i.e. the Last_Name is updated from NULL to \u201cThe Fierce\u201d in the above example.<\/p>\n In the article, we saw the most common options used with the INSERT INTO query in MySQL. The complete documentation of the INSERT INTO Clause is available here<\/a>.<\/p>\n In this article, we saw examples of the Insert into Query using my MySQL RDBMS. Attached are all the queries and also the creation scripts used.<\/p>\n3. SQL Insert Query Syntax<\/h2>\n
INSERT INTO table_name (column1, column2, column3, ...)\nVALUES (value1, value2, value3, ...);\n<\/pre>\n
INSERT INTO table_name \nVALUES (value1, value2, value3, ...);\n<\/pre>\n
4. INSERT Query in MySQL<\/h2>\n
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]\n [INTO] tbl_name\n [PARTITION (partition_name [, partition_name] ...)]\n [(col_name [, col_name] ...)]\n { {VALUES | VALUE} (value_list) [, (value_list)] ...\n |\n VALUES row_constructor_list\n }\n [AS row_alias[(col_alias [, col_alias] ...)]]\n [ON DUPLICATE KEY UPDATE assignment_list]\n\nINSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]\n [INTO] tbl_name\n [PARTITION (partition_name [, partition_name] ...)]\n [AS row_alias[(col_alias [, col_alias] ...)]]\n SET assignment_list\n [ON DUPLICATE KEY UPDATE assignment_list]\n\nINSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]\n [INTO] tbl_name\n [PARTITION (partition_name [, partition_name] ...)]\n [(col_name [, col_name] ...)]\n [AS row_alias[(col_alias [, col_alias] ...)]]\n {SELECT ... | TABLE table_name}\n [ON DUPLICATE KEY UPDATE assignment_list]\n\nvalue:\n {expr | DEFAULT}\n\nvalue_list:\n value [, value] ...\n\nrow_constructor_list:\n ROW(value_list)[, ROW(value_list)][, ...]\n\nassignment:\n col_name = [row_alias.]value\n\nassignment_list:\n assignment [, assignment] ...\n<\/pre>\n4.1 Setup for examples<\/h3>\n
CREATE TABLE EMPLOYEES(\n EMP_ID INT AUTO_INCREMENT PRIMARY KEY,\n FIRST_NAME VARCHAR(20) NOT NULL,\n LAST_NAME VARCHAR(20),\n BIRTH_DATE DATE DEFAULT '1900-00-00',\n HIRE_DATE DATE DEFAULT (CURRENT_DATE())\n);\n\nCREATE TABLE DEPARTMENTS(\n DEPT_ID INT AUTO_INCREMENT PRIMARY KEY,\n DEPT_NAME VARCHAR(30)\n);\n\nCREATE TABLE EMPLOYEE_DEPT(\n RECORD_ID INT AUTO_INCREMENT PRIMARY KEY,\n EMP_ID INT,\n DEPT_ID INT,\n FROM_DATE DATE,\n TO_DATE DATE,\n FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID),\n FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID)\n);\n<\/pre>\n
CREATE TABLE EMPLOYEES2(\n EMP_ID INT PRIMARY KEY,\n FIRST_NAME VARCHAR(20) NOT NULL,\n LAST_NAME VARCHAR(20),\n BIRTH_DATE DATE DEFAULT '1900-00-00',\n HIRE_DATE DATE DEFAULT (CURRENT_DATE())\n);\n<\/pre>\n
5. INSERT INTO Examples<\/h2>\n
5.1 Insert a single row in the table using column_names<\/h3>\n
INSERT INTO EMPLOYEES (FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE)\nvalues('Thorin','Oakenshield','2746-01-01','2760-05-01'); \n<\/pre>\n![\"SQL](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insert_all_values.jpg\")
5.2 Insert a single row in a table without column_names<\/h3>\n
INSERT INTO EMPLOYEES\nvalues(default,'Thrain','Oakenshield','2850-01-01','2910-05-01');\n<\/pre>\n
![\"SQL](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insert_without_column_names.jpg\")
5.3 Insert a single row in a table with a default value<\/h3>\n
INSERT INTO EMPLOYEES (FIRST_NAME,HIRE_DATE)\nvalues('Smaug','2600-05-01');\n<\/pre>\n![\"SQL](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insert_with_default_values.jpg\")
5.4 Insert with Not NULL constraint violation<\/h3>\n
INSERT INTO EMPLOYEES(LAST_NAME,BIRTH_DATE,HIRE_DATE)\nVALUES('Oakenshield','2650-09-12','3010-07-15'); <\/pre>\n![\"SQL](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insert_error_not_null_constraint.jpg\")
5.5 Insert Multiple records in a table<\/h3>\n
INSERT INTO DEPARTMENTS (DEPT_NAME)\nvalues\n('Logistics'),\n('Support'),\n('Development'),\n('Transport');\n<\/pre>\n![\"Multiple](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insert_multiple_values.jpg\")
5.6 Insert into table with Foreign keys constraint<\/h3>\n
INSERT INTO EMPLOYEE_DEPT (EMP_ID,DEPT_ID,FROM_DATE,TO_DATE)\nVALUES (1,3,'2760-05-15','2800-10-15');\n<\/pre>\n
![\"Insert](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insertion_foreign_keys.jpg\")
5.7 Foreign Key constraint violation<\/h3>\n
INSERT INTO EMPLOYEE_DEPT (EMP_ID,DEPT_ID,FROM_DATE,TO_DATE)\nVALUES (3,10,'2765-05-15','2900-10-15');\n<\/pre>\n
![\"Foreign](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/foreign_key_violated.jpg\")
5.8 Insert Into using a Select Query<\/h3>\n
INSERT INTO EMPLOYEES2 (EMP_ID, FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE)\nSELECT EMP_ID, FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE FROM EMPLOYEES WHERE EMP_ID =1;\n<\/pre>\n
INSERT INTO EMPLOYEES2 (EMP_ID, FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE)\nSELECT * FROM EMPLOYEES WHERE EMP_ID =2;\n<\/pre>\n
![\"Insert](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insert_into_select.jpg\")
5.9 Insert into using a Select without a where clause<\/h3>\n
INSERT INTO EMPLOYEES2 (EMP_ID,FIRST_NAME,LAST_NAME,BIRTH_DATE,HIRE_DATE)\nSELECT * FROM EMPLOYEES;\n<\/pre>\n
![\"Insert](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insert_select_all.jpg\")
5.10 Insert into with the ON DUPLICATE KEY UPDATE<\/h3>\n
INSERT INTO EMPLOYEES2 (EMP_ID,FIRST_NAME)\nVALUES (4,'Bofur');\n<\/pre>\n
![\"Unique](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/primary_key_violation.jpg\")
INSERT INTO EMPLOYEES2 (EMP_ID, FIRST_NAME)\nVALUES (4,'Smaug')\nON DUPLICATE KEY UPDATE LAST_NAME=\"The Fierce\";\n<\/pre>\n
![\"Insert](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/11\/insert_on_duplicate_key.jpg\")
6. Summary<\/h2>\n
7. Download the Source Code<\/h2>\n