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

Popular SQL Courses

Introduction to SQL

Beginner
2 hr
265.8K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related
A duck codes in DuckDB

DuckDB makes SQL a first-class citizen on DataCamp Workspace

In this blog post, we list out all recent improvements that make it seamless and efficient to query data with SQL, all without leaving the tool.
Filip Schouwenaars's photo

Filip Schouwenaars

The 80 Top SQL Interview Questions and Answers for Beginners & Intermediate Practitioners

This article provides a comprehensive overview of 80 essential SQL questions and answers for job hunters, hiring managers, and recruiters, covering both general topics and technical questions.
Elena Kosourova's photo

Elena Kosourova

33 min

Revealing the Winners of Netflix's Top 10 Charts Competition

Explore the winners of a recent competition analyzing the most common attributes of popular Netflix content.
Luigi D'Introno's photo

Luigi D'Introno

4 min

Navigating Parenthood with Data

Emily Oster dives into how data can help guide our parenting.
Richie Cotton's photo

Richie Cotton

45 min

How to Use SQL in pandas Using pandasql Queries

Unleash the power of SQL within pandas and learn when and how to use SQL queries in pandas using the pandasql library for seamless integration.
Elena Kosourova's photo

Elena Kosourova

8 min

How to Practice SQL Using any Dataset with Workspace

Learn how DataCamp Workspace optimizes the experience of working with Jupyter notebooks and SQL. Discover how to effortlessly write SQL queries, connect to databases, analyze CSV files, and leverage the power of AI assistance
Richie Cotton's photo

Richie Cotton

9 min

See MoreSee More