Using INSERT SQL command

Data Manipulation Language (DML) provides SQL Commands that are used for managing data in the database. DML commands are not auto-committed. It means changes made by DML Commands are not permanent to the database, it can be rolled back.

Data Manipulation Language supports three main commands. These are: INSERT, UPDATE and DELETE commands. In this article, we will discuss only the INSERT statement in SQL.

SQL INSERT Statement

SQL INSERT command, as the name suggests, is used for inserting one or more records to a table in a database. For example, whenever we post a tweet on Twitter, the text or tweet gets inserted in a table as a new record.

Syntax:

INSERT INTO table_name(column1, column2, ...) VALUES(data1, data2, ...);

Let us consider a table 'student' with the following three fields. We will insert some records in this table using the INSERT command.

Data can be stored in a table in different ways. We can insert records in a table with or without specifying the column names and also by using the SQL SELECT statement. Let us discuss these ways with the help of examples.

Example: Insert Records without Specifying the Column Names

In this example, we use INSERT INTO statement to insert data into the table. This example does not take any column names. You can directly pass data inside parentheses according to the fields.

INSERT INTO student VALUES(101, 'Adam', 15);

Output:

Example: Insert Records by Specifying the Column Names

We can also use the INSERT command to insert values for some specific columns only of a row. In this example, we specify the column names along with the values to be inserted. This statement also inserts a NULL value into the age column of the student table. The below SQL query will only insert id and name values in the newly inserted record.

INSERT INTO student(id, name) VALUES(102, 'Alex');
or
INSERT INTO student VALUES(102,'Alex', NULL);

Output:

Example: Insert Default Value to a Column

This example makes use of the DEFAULT constraint. By writing DEFAULT instead of data value, it uses that value that is provided during the creation of the table. Suppose the column age in our table has a default value of 14. Therefore, if you run the below SQL query, it will insert the default value into the age column.

INSERT INTO student VALUES(103,'Chris', DEFAULT);
or
INSERT INTO student VALUES(103,'Chris');

Output:

Example: Insert Multiple Records using a SELECT Statement

Using the below SQL statement, you can add column values from another table to a new table depending on the conditions and user's choices. This method is used for more complicated cases of insertion.

INSERT INTO table  
(column1, column2, ... column_n )  
SELECT expression1, expression2, ... expression_n  
FROM source_table
WHERE conditions; 

Example: Insert Records with Oracle Database

Oracle database supports INSERT ALL statement to add multiple records simultaneously. SELECT * statement is also used with INSERT statement to insert records. Make sure not to use a semicolon at the end of INSERT statements to avoid an error.

INSERT ALL  
  INTO student (id, name, age) VALUES (101, 'Derek', 15)  
  INTO student (id, name, age) VALUES (102, 'David', 16)  
  INTO student (id, name, age) VALUES (103, 'Denver', 15)  
SELECT * FROM dual; 

Output:

Note: Whenever you add a new row, you should make sure that the data type of the value and the column should be matched. Also, if any integrity constraints are defined for the table, you must follow them while inserting records like DEFAULT in this article.

Conclusion

In this article, we discussed the SQL INSERT query using different examples. We also discussed Oracle syntax to insert records in a table.

---

---