Skip to main content

INSERT INTO SQL FUNCTION

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

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

Data Manipulation in SQL

Beginner
4 hours
155,983
Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.
See DetailsRight Arrow
Start Course

Introduction to SQL

Beginner
2 hours
114,604
Learn how to create and query relational databases using SQL in just two hours.

Intermediate SQL

Beginner
4 hours
39,060
Accompanied at every step with hands-on practice queries, this course teaches you everything you need to know to analyze data using your own SQL code today!
See all coursesRight Arrow
Related

How to Become a Data Analyst in 2023: 5 Steps to Start Your Career

Learn how to become a data analyst and discover everything you need to know about launching your career, including the skills you need and how to learn them.
Elena Kosourova 's photo

Elena Kosourova

18 min

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

Sports Analytics: How Different Sports Use Data Analytics

Discover how sports analytics works and how different sports use data to provide meaningful insights. Plus, discover what it takes to become a sports data analyst.
Kurtis Pykes 's photo

Kurtis Pykes

13 min

SQL Window Functions Cheat Sheet

With this SQL Window Functions cheat sheet, you'll have a handy reference guide to the various types of window functions in SQL.
DataCamp Team's photo

DataCamp Team

10 min

COALESCE SQL Function

COALESCE() is one of the handiest functions in SQL. Read this tutorial to learn how to master it.
Travis Tang 's photo

Travis Tang

How to Write a Bash Script: A Simple Bash Scripting Tutorial

Discover the basics of bash scripting and learn how to write a bash script.
Kurtis Pykes 's photo

Kurtis Pykes

5 min

See MoreSee More