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.

EmpId FirstName LastName Email PhoneNo Salary
           

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.

SQL Script: Insert a Record in the Table
INSERT INTO Employee
VALUES(1,'John','King','[email protected]','123.123.1834',33000);

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

EmpId FirstName LastName Email PhoneNo Salary
1 'John' 'King' '[email protected]' '123.123.1834' 33000
Note:
Any change in the sequence, the number of values, or its data type may result in an error or incorrect data.

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.

SQL Script: Insert Data to Specific Columns
INSERT INTO Employee(EmpId, FirstName, LastName)
VALUES(2,'James','Bond');

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

EmpId FirstName LastName Email PhoneNo Salary
1 'John' 'King' '[email protected]' '650.127.1834' 33000
2 'James' 'Bond'

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

Note:
You must insert data to all NOT NULL columns; otherwise, it will raise an error.

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.

SQL Script: Insert Multiple Records
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.

EmpId FirstName LastName Email PhoneNo Salary
1 'John' 'King' '[email protected]' '123.123.1834' 33000
2 'James' 'Bond'
3 'Neena' 'Kochhar' '[email protected]' '123.456.4568' 17000
4 'Lex' 'De Haan' '[email protected]' '123.456.4569' 15000

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.

SQL Script: Insert Multiple Records in Oracle
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.

EmpId FirstName LastName Email PhoneNo Salary
1 'Renske' 'Ladwig'
2 'Laura' 'Bissot' '123.456.444'
3 'Kevin' 'Weiss' 25000

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.

SQL Script: Insert Multiple Records in Oracle
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.

SQL Script: Insert Multiple Records in Multiple Tables in OracleInsert Records From Another 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.