Skip to main content
HomeTutorialsSQL

INSERT INTO SQL FUNCTION

INSERT INTO lets you add data to your tables. Learn how to use it in this tutorial.
Oct 2022  · 3 min read

What is the INSERT INTO function?

The INSERT INTO function inserts new records into a table. 

When to use INSERT INTO

INSERT INTO is useful when new rows need to be added to a table on an ad-hoc basis. 

To execute the INSERT INTO function, you must have write access to the underlying table. You can request write access from your database administrator. 

INSERT INTO syntax

The INSERT INTO syntax includes the column names and the values to be inserted. 

INSERT INTO table_name (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

The column names are optional if the row to be inserted contains values for all columns. The following syntax will add the values value1, value2, value3,..., valueN in the same order as the columns of the table. 

INSERT INTO table_name 
VALUES (value1,value2,value3,...valueN);

You can also insert rows from another table. The syntax is as follows. Again, the column names are optional if the row to be inserted contains values for all columns. 

INSERT INTO table_1 (column1, column2, …, columnN)
   SELECT column1, column2, …, columnN
   FROM table_2;

In some versions of SQL like Transact-SQL, you can also display the inserted values with the OUTPUT clause.

INSERT INTO table_1 (column1, column2, …, columnN)
   OUTPUT inserted.column1
   VALUES (value1,value2,value3,...valueN);

Once successfully executed, the following message will tell you that you have successfully added n rows to the database.

You have made changes to the database. Rows affected: n

INSERT INTO examples

Consider the employees table that has 5 columns. 

EmployeeID

LastName

FirstName

BirthDate

Photo

1

Davolio

Nancy

1968-12-08

EmpID1.pic

2

Fuller

Andrew

1952-02-19

EmpID2.pic

Source: Microsoft's Northwind Database

Example 1. Adding one row to the employees table

INSERT INTO employees ('EmployeeID','FirstName','LastName')
VALUES (11, 'Travis', 'Tang');
SELECT * FROM employees;

EmployeeID

LastName

FirstName

BirthDate

Photo

11

Tang

Travis

null

null

Example 2. Adding rows with no null values.

Since the row contains values from all columns, there is no need to specify the name of the columns. 

INSERT INTO employees 
VALUES (11, 'Tang','Travis',  '1900-01-01', 'travis.jpeg');
SELECT * FROM employees;

EmployeeID

LastName

FirstName

BirthDate

Photo

11

Tang

Travis

1900-01-01

travis.jpeg

Example 3. Insert multiple rows 

INSERT INTO employees ('EmployeeID','FirstName','LastName','BirthDate')
VALUES 
   (11, 'Travis', 'Tang','1909-01-01'),
   (12, 'Ariana', 'Venti', '1901-01-01');
SELECT * FROM employees;

EmployeeID

LastName

FirstName

BirthDate

Photo

11

Tang

Travis

null

null

12

Venti

Ariana

1901-01-01

null

Example 4. Insert rows from one table to another

Consider this scenario. A company engaged the services of a part-time contractor who has just become a full-time employee of the company. We can use INSERT INTO to add the row of the contractor (named Kanye East) from the contractor table to the employee table.

INSERT INTO employees (LastName, FirstName, BirthDate, Photo)
   SELECT 
      13 as EmployeeID, 
      LastName, 
      FirstName, 
      BirthDate,
      Photo
   FROM Contractor 
   WHERE ContractorID = 500;

SELECT * FROM employees 
WHERE EmployeeID = 13;

EmployeeID

LastName

FirstName

BirthDate

Photo

13

East

Kanye

1902-01-01

kanye.jpeg

Technical requirements

INSERT INTO is available to all versions of SQL.

See also

Learn more about SQL

Topics

Popular SQL Courses

Course

Introduction to SQL

2 hr
660.8K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

tutorial

Insert Into SQL Tutorial

SQL's "INSERT INTO" statement can be used to add rows of data to a table in the database.
DataCamp Team's photo

DataCamp Team

3 min

tutorial

COUNT() SQL FUNCTION

COUNT() lets you count the number of rows that match certain conditions. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

tutorial

FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

tutorial

Hacking Date Functions in SQLite

In this tutorial, learn how to use date functions in SQLite.
Hillary Green-Lerman's photo

Hillary Green-Lerman

3 min

tutorial

Aggregate Functions in SQL

Learn how to use aggregate functions for summarizing results and gaining useful insights about data in SQL.
Sayak Paul's photo

Sayak Paul

9 min

code-along

Getting Started in SQL

Learn how to write basic queries in SQL and find answers to business questions.
Kelsey McNeillie's photo

Kelsey McNeillie

See MoreSee More