Insert Query in SQL

In this SQL guide, we will explore what an insert query is in SQL and how to use the insert statement to insert the data into the tables. Furthermore, we will also show you how we can insert the multiple records in a database table using the insert query. You can download the Microsoft SQL server from its official website to run the following SQL queries.

Basic Purpose of Insert Query in SQL

The insert query in SQL inserts new rows or records into a table. Using this SQL insert statement, you can insert one or multiple records into a database table.

We use the “INSERT INTO” statement to insert the data into a table that is also a part of Data Manipulation Language (DML). It is a computer programing language that allows you to perform the basic CRUD (Create, Update, Delete) operations on the database tables. DML is a sublanguage of SQL that enables us to insert the records or rows into the tables. It also allows us to modify and retrieve the information from the database objects.

Using the insert query, you can insert the values into all or selected columns of a table. You can insert the data by creating a new table using the “CREATE TABLE” command or also insert the data in an existing table.

What Is the Syntax of Insert Query in SQL?

The “INSERT INTO” statement in SQL is used for data insertion.  The insert query is used in two different ways. But a proper syntax or set of rules is used to insert the data into a table. The syntax of the insert query is as follows:

• First, specify the table name and enter the column names in parenthesis that are separated by a comma.
• Use the “VALUES” clause and enter the values in the parenthesis that you want to insert in a table.

If you want to insert the values into every column of the table, you do not need to mention the column names in the insert query. However, it is important to note that the order of the values should be matched with the columns in the table. In this case, the insert query syntax is:

VALUES (value1, value2, value3, …,valueN);

How to Insert a Single Record Into a Table Using the Insert Query in SQL

You can insert a single record into a table using the “INSERT INTO” statement on SQL. In the following example, we demonstrated from scratch on how to create a table and insert a single record into it:

In the previous SQL query, we created an “Employee_tbl” table where we set the “Employee_id” filed as the primary key. We assigned the datatype “varchar()” and not null constraints on different columns of the Employee table. Here, the Employee_firstname must contain the values. It does not contain the null values or is left empty.

Important Note: 

When you insert the new records into a table using the insert query in SQL, you should provide the values for every NOT NULL constrained column. But if a column allows the NULL values, you can omit those column values from the INSERT INTO statement.

As we mentioned earlier, you can insert the single or multiple records in a table using the insert query in SQL. To insert a new record or a row into a table using an insert statement, use the table name along with the VALUES keyword. Let’s take a simple example in which we insert a single record into a table using an insert statement in SQL.

Example:

We have company employee record in our databases. After recruiting a new resource or employee, we need to add a new record of this employee in the “Employees_table”. How do we do it in SQL? We can add a new employee record using the insert query.

The previous SQL insert statement adds a new record to the “Employees_table”.  Here, the (Employee_id, Employee_firstname, Employee_lastname, Employee_Address, Employee_email, Employee_Joining, Employee_Country, Employee_City) are the column names in which we inserted a new employee record. If the column doesn’t have any Not Null constraint, you can leave this field empty or ignore the column name and its value while writing the insert statement.

Important Note: 

When you add the new values to a table, you make sure that the “Values” data type should be matched with the column. You must follow all integrity constraints that are defined during the table creation.

To verify that the data are inserted into a table, use the “Select” statement. This statement retrieves the data from the employee table and shows it in a tabular form.

The following output should display on the screen after running the previous statement:

We inserted the data into the “Employee_id” field because we did not set the “auto-increment” feature on this field or column. Therefore, if you insert a new record into a table, it doesn’t generate a new number automatically. The Auto-increment feature usually assigns to a primary key field in a table that automatically generates a new number when a new record is inserted into a table.

How to Insert the Data Into Specific Columns Using the Insert Query in SQL

You can also insert the data in specific columns using the insert query in SQL. To do this, mention only the column names and values in which you want to insert the data.

Example:

In the following SQL insert statement, we insert a new record in specific columns. Here, we insert the data in the “Employee_firstname“, “Employee_lastname“, “ Employee_Country”, and “Employee_City” columns (Employee_id is updated automatically). Refer to this SQL insert statement:

Now, you can verify the result using the “Select” statement:

The following output is received in the results section:

How to Insert Multiple Rows in the SQL Table Using the Insert Query

It is a common practice for developers that they need to insert the multiple records into a database table. To do this, we have to write an insert statement multiple times to insert the values in a table which is a more time-consuming task.

In this situation, utilizing a single SQL statement to insert several records is a best practice. Yes, you can use a single SQL insert query to add multiple records to a table. Use the following syntax to insert the multiple records using a single SQL insert query:

Example: 

In the following SQL insert statement, we inserted the multiple Employee records in the “Employee_tbl” table using the single insert query:

You can also insert the previous three records in a table using multiple insert statements in this way:

Execute the following SQL statement to see the output:

How to Copy the Content of One Table Into Another Using the Insert Select Statement

The SQL insert query also helps you to copy the records from one table to another. For this purpose, use the INSERT in combination with the Select statement that copies the rows from one SQL table to another. The syntax of Insert with select statement is:

The given syntax is similar to the simple insert query. But the difference is that we use a “Select” statement here instead of the “Values” clause. The “Select” statement retrieves the records or rows from another table. This insert into select statement is very useful in scenarios where you want to keep the backup of a table using the SQL Queries.

Example 1:

Let’s take an example to demonstrate the significance of the insert query with a select statement. Here, we use the “tbl_Employee” table of a company database. We create another table named “tbl_Employee”. The table structure of both tables, “tbl_Employee” and “tbl_updatedEmployeeinfo”, is the same. In addition, we drop the foreign keys and few table constraints on the “tbl_updatedEmployeeinfo”.

First, create a table named “tbl_updatedEmployeeinfo” by executing the following SQL query:

Now, we insert the data from the “tbl_Employee” table to the “tbl_updatedEmployeeinfo” table using the following query:

insert into tbl_updatedEmployeeinfo select Employee_id, Employee_firstname, Employee_lastname, Employee_Address, Employee_email, Employee_Joining, Employee_Country, Employee_City from Employee_tbl;

Now, to verify the output, execute the following “Select” query:

As we noticed in the previous screenshot, the data of “Employee_tbl” is copied to a new “tbl_updatedEmployeeinfo” table.

Example 2:

You can also insert specific or all records from all columns of a table into another new table. For this purpose, you don’t need to specify all column names. Just use this simple syntax:

Let’s take an example. Suppose we want to duplicate our “Employee_tbl” table and name it as “EmployeeBackup”. This table copy should include the same columns from the original Employee table. To do this, create a “tbl_EmployeeBackup” table with similar column names. Now, we don’t need to specify the names of the columns separately. We can use the following insert query with the select statement to create the backup of all the columns from the original table to another table:

Now, we can verify the output by retrieving the table’s data.

As we can see in the following screenshot, a backup of the Employee table is created successfully. You can back up your large records in this way.

Why Do We Use the INSERT IGNORE Statement?

INSERT IGNORE is a beneficial statement in SQL. For example, the entire INSERT query may be aborted due to an error while it is processing when entering the thousands of records into a table. In this case, we need to run this entire insert query again. The INSERT IGNORE statement can be used to resolve the problems of this kind.

The INSERT query does not fail if we use the INSERT IGNORE command to insert the bulk data into the tables regardless if the query experiences an error.

Conclusion

We demonstrated in this guide on how to use the insert query in SQL. You can use an insert query for data insertion in a table. We discuss how you can insert a single, multiple, or specific records in a table using the insert statement. Moreover, you can easily create the backup of your large tables using the insert query with select statement. I hope this SQL guide will give you a basic understanding of the insert statement. If you need more details, you can visit the official Microsoft documentation.