INSERT Query SQL – All You Need to Know about the INSERT statement

Structured Query Language or most commonly known as SQL, is one of the most popular languages used in relational databases. This language is used to handle databases and manipulate data with the help of queries. One such query is the Insert Query. So, in this article on Insert Query SQL, you will understand the INSERT INTO statement in the following sequence:

1. 1. What is INSERT Query in SQL?
   2. INSERT INTO Syntax
   3. Example of INSERT Statement
   4. Using SELECT Query in INSERT INTO 

What is INSERT Query in SQL?

The SQL INSERT INTO statement is used to add new tuples into a table, for a database. With the help of this SQL query, you can insert data either into a specific column or for all columns in general. Also, you can insert data to a specific table from another table for a single row or multiple rows. So, now that you what is INSERT query in SQL, let us move forward and see the syntax of this query. 

INSERT INTO Syntax

There are two ways through which you can implement the INSERT query.

With Column Names and Values

INSERT INTO Tablename (Column1, Column2, Column3, ...,ColumnN) VALUES (Value1, Value2, Value3, ...);

With Values

INSERT INTO Tablename VALUES (Value1, Value2, Value3, ...);

Note: When you use the second method, you have to make sure that the values are mentioned in the same order as that of column names.

So, now, that you know the syntax of the INSERT statement, next in this article on Insert query SQL, let us see an example.

Learn more about SQL Server and its framework from the SQL Server Certification.

Example of INSERT Statement

Consider the following table with the table name as SampleData: 

Now, let us say you want to insert a row in this table. Then, you can use either of the above syntaxes in the following way:

#With column names and values
INSERT INTO SampleData(ID, Name, Age, PhoneNumber, Salary) VALUES ('6', 'Rohit','25', '9924388761', '35000');
#With values only
INSERT INTO SampleData VALUES ('6', 'Rohit','25', '9924388761', '35000');

Once you execute the query, you will see the below output:

Well, this was about inserting a new record to a table. But, there may be a few other scenarios, where you would want to use SQL. The scenarios can be as follows:

• How do I copy specific rows from a table?
• What is the way to insert all columns of a table to another table?
• How can I insert specific columns of a table into another table?

The answer to these questions is by using the SELECT statement with the INSERT statement. So, next in this article on Insert Query SQL, let us understand how to use the SELECT statement in INSERT INTO.

Using SELECT Query in INSERT INTO 

The SELECT query is used along with the INSERT INTO statement to select the data from another table. The following are the various ways to use the SELECT statement with the INSERT query in SQL:

1. Copy specific rows from a table
2. Insert all columns of a table
3. Inserting specific columns of a table

Copy specific rows from a table

You can insert a specific set of rows from a table to another table by using the SELECT statement with the WHERE clause.

Syntax:

INSERT INTO Table1 SELECT * FROM Table2 WHERE condition;

Here, you are trying to insert values from Table2 to Table1 based on a condition. 

Example:

Consider an example, where you have to insert a few rows from our above table(SampleData) to a new table (New_Data) based on the condition Age > 30

INSERT INTO New_Data SELECT * FROM SampleData WHERE Age > 30;

Output:

Insert all columns of a table

You can insert all the columns from a table to another table by using the asterisk(*) with INSERT INTO query.

Syntax:

INSERT INTO Table1 SELECT * FROM Table2;

Here, you are trying to insert values all columns from Table2 to Table1.

Example:

Consider an example, where you have to insert all columns from our above table(SampleData) to a new table (ExampleData).  Also, consider that ExampleData already has the following data present:

Now, execute the following query to insert all the columns and rows from SampleData to the above table.

INSERT INTO ExampleData SELECT * FROM SampleData;

Output:

Inserting specific columns of a table

You can insert a specific set of columns from a table to another table by using the SELECT statement.

Syntax:

INSERT INTO Table1(Column_Names) SELECT Column_Names FROM Table2;

Here, you are trying to insert specific columns from Table2 to Table1.

Example:

Consider an example, where you have to insert columns(ID, Name )  from the table(ExampleData) to the table(SampleData). 

INSERT INTO SampleData(ID,Name) SELECT ID, Name, FROM ExampleData;

Output:

With this, we come to an end to this article on Insert Query SQL. I hope you understood how to use INSERT INTO query in SQL. We have seen the various ways to use the INSERT query. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”Insert Query SQL” and I will get back to you.