SQL - INSERT Data into a Table

The INSERT statement is used to insert single or multiple records into a table.

Syntax:

INSERT INTO table_name(column_name1, column_name2...column_nameN) 
VALUES(column1_value, column2_value...columnN_value);

For the demo purpose, the following Employee table will be used in all examples here.

Insert Values to All Columns

To insert values to all columns of a table, you don't need to specify column names with the table name. Specify the values for each column in a sequence as they appear in the table.

The following statement will insert a single row in all columns of the above Employee table in the SQL Server, Oracle, MySQL, SQLite, and PostgreSQL database.

INSERT INTO Employee
VALUES(1,'John','King','[email protected]','123.123.1834',33000);

Now, the Select * from Employee; query will display the following result.

Insert Values to Specific Columns

Mention the column names in the INSERT statement to insert data to some specific columns of a table.

The following INSERT statement will add a new record to the Employee table in EmpId, FirstName, and LastName columns. Note that the INSERT statement requires the column names in the parenthesis if you don't want to insert data in all the columns but to some specific columns only.

INSERT INTO Employee(EmpId, FirstName, LastName)
VALUES(2,'James','Bond');

Now, the Select * from Employee query will display the following result.

As you can see above, the columns that does not have data contains a null value.

Insert Multiple Records

Insert multiple records in a single INSERT INTO statement by having mulitiple records in parenthesis after VALUES. The following will insert two records in the Employee table in SQL Server, MySQL, PostgreSQL, SQLite database.

INSERT INTO Employee 
VALUES 
(3,'Neena','Kochhar','[email protected]','123.456.4568',17000),
(4,'Lex','De Haan','[email protected]','123.456.4569',15000);

Now, the Select * from Employee query will display the following result.

Insert Multiple Records in Oracle

Use INSERT ALL statement to add multiple records using a single INSERT statement into single or multiple tables at the same time.

Syntax:

INSERT ALL

  INTO table_name (column_name1,column_name2,...)
  VALUES(value1,value2,...)

  INTO table_name (column_name1,column_name2,...)
  VALUES(value1,value2,...)

Subquery

As per the syntax of INSERT ALL, each value expression value1, value2 must refer to a column returned by the select list of the subquery. If you want to specify literal values, then use SELECT * FROM Dual; as a subquery.

The following INSERT ALL will insert three records to the Employee table, where each record has a different set of columns.

INSERT ALL
  INTO Employee(EmpId, FirstName, LastName)VALUES(1,'Renske','Ladwig')
  INTO Employee(EmpId, FirstName, LastName, PhoneNo) VALUES(2,'Laura','Bissot', '123.456.444')
  INTO Employee(EmpId, FirstName, LastName, Salary) VALUES(3, 'Kevin','Weiss', 25000)
SELECT * FROM Dual;

After executing the above query, the Employee table will look like below.

The INSERT ALL can be used to insert multiple records into multiple tables. Bellow, the INSERT ALL statement will insert one record to the Employee table, and two records to the Customer table with a different set of columns.

INSERT ALL
  INTO Employee (EmpId, FirstName, LastName) VALUES(4,'Sachin','Tendulkar')
  INTO Customers(CustId, FirstName, LastName, Country) VALUES(1, 'Laura','Bissot', ‘USA')
  INTO Customers(CustId, FirstName, LastName, Gender, Country) VALUES(2, 'Kevin','Weiss','M','ROME')
SELECT * FROM Dual;

We can also insert multiple records to one table by selecting data from another table, provided the other table has the required fields to populate the first table.

INSERT INTO Employee(EmpId, FirstName, LastName)
  SELECT CustId, FirstName, LastName FROM Customer

The above INSERT statement inserts data from the Customer table into the Employee table where CustId values would be inserted as EmpId.