SQL INSERT Query | Insert Data in Table

SQL INSERT INTO Query inserts new rows in a table in an SQL database. It adds new rows by specifying the column values for each row. In this article, we will discuss the INSERT INTO Query in SQL. Also, we will discuss a few examples of writing SQL INSERT Queries.

Syntax

There are two possible syntax for an INSERT INTO Query.

Without Specifying Column Names

If you want to insert a new value for every column of the table, you don’t need to specify individual column names of all the columns. However, make sure that that the values are in the same order as the columns in the table.

INSERT INTO table_name VALUES (value1, value2, … valueN);

Only Adding Specific Columns

However, if you want to add only a few columns in a row and leave the rest default, you must specify the column names with the values in the same order.

INSERT INTO table (column1, column2 … columnN) VALUES (value1, value2, … valueN);

Also, you can add multiple rows at once by using a single INSERT Query in SQL.

Demo Table

For instance, consider the demo table to refer to all the queries in this article.

mysql> SELECT * FROM employees;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

Examples

Let’s discuss a few examples of adding data in table using SQL INSERT INTO Query.

Example 1: Inserting With All Columns

For instance, let’s insert a new row by adding all the columns in the table using INSERT Query in SQL.

INSERT INTO employees VALUES (6, 'Joe', 20000, 10, 'Beta');
Query OK, 1 row affected (0.06 sec)

When we want to add values to all the columns in the row, we do not need to specify the list of columns of the table. However, the order of the values should be the same as the order of columns of the table. You can view the new row by using the SQL WHERE Clause as follows:

mysql> SELECT * FROM employees WHERE name = 'Joe';
+----+------+--------+------------+------+
| id | name | salary | experience | team |
+----+------+--------+------------+------+
|  6 | Joe  |  20000 |         10 | Beta |
+----+------+--------+------------+------+

Example 2: Inserting Only Specific Columns

Similarly, you can insert only specific columns by specifying the column list in the query.

INSERT INTO employees (name, salary, team) VALUES ('Janice', 30000, 'Alpha');

The columns which are not specified in the column list will assume their default value.

SELECT * FROM employees WHERE name = 'Janice';
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
| 14 | Janice |  30000 |       NULL | Alpha |
+----+--------+--------+------------+-------+

In the above example, the columns id and experience get their default values.

Example 3: Multiple Rows in Single INSERT Query

Also, you can use a single SQL INSERT Query to add multiple rows in the table by separating the VALUES portion with a comma. Consider the following example:

INSERT INTO employees (name, salary, experience, team) VALUES ('Bruce', 4000, 1, 'Alpha'), 
('Emily', 24000, 12, 'Beta'), 
('Jim', 12000, 8, 'Gamma');

The above query, adds three new rows in the table. Therefore, you can use a single INSERT Query to add multiple rows in SQL.

Conclusion

In conclusion, we discussed the INSERT INTO Query in SQL. You can read more about it on Wikipedia. Additionally, you can also learn about more SQL Queries on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *