SQL Insert Query With Examples [Latest]

SQL Insert Query

SQL INSERT statement inserts data into a table of the database. There are three ways to insert data into database using the INSERT INTO statement. Let’s try to understand all the ways we can use insert query in sql statements.

SQL Insert Examples

The three ways to use INSERT INTO statement are as mentioned below.

1. SQL Insert with Column Names

   In order to insert data into a table we have to specify the columns and the corresponding values as shown below.

   Syntax:

   INSERT INTO table_name (column(s)) VALUES value(s);

   1 2 3

   INSERT INTO table_name (column(s)) VALUES value(s);

   In the syntax mentioned above the values should be in the same order as the column mentioned in the INSERT INTO statement. Otherwise it might throw error or data might go into wrong columns. Here is an example of sql insert with column names.

   INSERT INTO Customer (FirstName, LastName, City, Country) VALUES ('John', 'Smith', 'New York', 'USA');

   1 2 3 4

   INSERT INTO Customer (FirstName, LastName, City, Country) VALUES ('John', 'Smith', 'New York', 'USA');

2. SQL Insert Query without Column Names

   In order to insert data into a table without specifying the columns, it is important to provide the values in the same order as the columns are present in the table. Also, this should be used only when we want to insert data into every column of the table.

   Syntax:

   INSERT INTO table_name VALUES value(s).

   1 2 3

   INSERT INTO table_name VALUES value(s).

   Example:

   INSERT INTO Customer VALUES ('Amit, 'Kumar, 'Bangalore', 'India');

   1 2 3

   INSERT INTO Customer VALUES ('Amit, 'Kumar, 'Bangalore', 'India');

   This type of insert is error prone, specially when you add another column in the table later on. The data might get corrupted because of getting inserted into wrong columns, this type of insert query should be avoided wherever possible.

3. SQL INSERT INTO SELECT

   SQL INSERT statement can be used along with the SELECT statement. This is usually used when we have to insert data from one table to other table.

   Syntax:

   INSERT INTO first_table_name [column(s)] SELECT column(s) FROM second_table_name WHERE condition;

   1 2 3

   INSERT INTO first_table_name [column(s)] SELECT column(s) FROM second_table_name   WHERE condition;

   Example:

   INSERT INTO Customer (FirstName, LastName, City, Country) SELECT LEFT(ContactName, CHARINDEX(' ',ContactName) - 1), SUBSTRING(ContactName, CHARINDEX(' ',ContactName) + 1, 100), City, Country FROM Supplier WHERE CompanyName="Sql"

   1 2 3

   INSERT INTO Customer (FirstName, LastName, City, Country) SELECT LEFT(ContactName, CHARINDEX(' ',ContactName) - 1), SUBSTRING(ContactName, CHARINDEX(' ',ContactName) + 1, 100), City, Country FROM Supplier WHERE CompanyName="Sql"

SQL Insert Multiple Rows

There are situations when multiple record needs to be inserted in the database table. Let’s try to understand how to accomplish such requirements.

Using the below mentioned syntax we can make multiple insertion in the database table.

Example:

In the above mentioned example multiple rows will be inserted in the Student table.

SQL Insert Date

Before I conclude SQL insert tutorial, let’s see how to insert date into tables. This is slightly tricky because there are vendor specific functions to get current date and time. Also there are vendor specific functions to parse string to date. Below queries will work for inserting date in MySQL database table.

Corresponding insert queries for Oracle database.

That’s all for insert query in sql statements.

Reference: Oracle Documentation