For adding new data in the SQL database tables, you may use the INSERT INTO statement.
The SQL INSERT INTO statement allows you to enter the data in two ways.
By using the column names syntax
INSERT INTO table_name (col1, col2, col3 ... ) VALUES (val1, val2,val3 ... );
Without column names syntax
INSERT INTO table_name VALUES (val1, val2,val3 ... );
- In the above syntax, you can see the usage of VALUES keywords. This is required in both ways as entering the data into tables.
- You may enter one or more records while using the INSERT INTO statement.
The example of using column names in INSERT INTO statement
As you enter data by using the column names of the table, you may use a different sequence than the actual physical structure of the table.
However, the column name sequence and the field values in parenthesis after the VALUES keyword must match.
The example below shows specifying the column names which is followed by using the VALUE keyword and the respective values of columns.
For that, I used our example table, sto_employees which keeps employees’ information in a departmental store.
The INSERT Query:
INSERT INTO sto_employees (id, emp_name, emp_age, emp_salary, join_date, phone) VALUES (9, 'Haynes', 25, 3300, '2018-03-20', '042-4521-458452');
- All the column names are given and so are their respective values. In the second resultset, a row is added to the table.
- The values contain int, varchar, and date data types. You can see that the int value is given without any quote.
- The text values for the varchar type are enclosed in single quotes.
- Similarly, for inserting date type, the value is enclosed in the single quotes.
INSERT INTO example without column names
This example enters a row into our example table without specifying the column names in the INSERT SQL statement.
As mentioned earlier, the sequence of values must be the same as the sequence of columns in the physical table. Otherwise, the INSERT query will try entering the record as provided in VALUES(), and data types of the provided value and column’s type may mismatch.
This may result in an error if the data is not matched. Even if the data type matches, the wrong values can be entered than expected (if you are not sure of the sequence).
See the example below for entering a record without providing the column names:
The INSERT query without column names:
INSERT INTO sto_employees VALUES (10, 'Maria', 23, 3000, '2018-02-15', '321-1041-254241');
What if a column name value is not provided?
If your table has columns that allow NULL values, these can be skipped by using the INSERT statement.
The better practice for using INSERT INTO statement, in that case, will be using column names.
The INSERT query in SQL below shows entering a record with only specific column names. I omitted the phone and salary fields:
INSERT INTO sto_employees (id, emp_name, emp_age, join_date) VALUES (11, 'Hina', 35, '2018-03-20');
In the table graphic, the fields whose values are not provided are showing NULL after entering the record into the employee’s table.
Add multiple records in a single INSERT query example
By writing a single INSERT INTO query, you may enter multiple records into the specified table.
For adding multiple rows, write the query of INSERT INTO till values (with or without column names), enclose the set of values in parenthesis for records, and separate those by commas.
See a demonstration below for entering multiple records by a single query:
INSERT INTO sto_employees (id, emp_name, emp_age, join_date) VALUES (12, 'Raina', 27, '2018-04-20'), (13, 'Jellani', 45, '2018-01-20'), (14, 'Reema', 34, '2018-02-20');
The resultset after entering data:
The graphic above shows that three records are added.
Inserting data from another table by INSERT INTO SELECT
You may copy data from one table to another by using the INSERT command with a SELECT statement.
In that case, the data type of the columns in the source and target tables must match, otherwise, an error is raised.
For using the SELECT statement with INSERT, use the following syntax:
INSERT INTO target_table (col1, col2, col3….) SELECT respective_col1, respective_col2, respective_col3… FROM source_table [WHERE condition];
A detailed guide is written about using the INSERT with a SELECT statement here.