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

Certification available

Course

Introduction to SQL

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

10 Top Data Analytics Conferences for 2024

Discover the most popular analytics conferences and events scheduled for 2024.
Javier Canales Luna's photo

Javier Canales Luna

7 min

Top 5 SQL Server Certifications: A Complete Guide

Unlock SQL Server certification success with our guide on paths, preparation with DataCamp, and the top certifications to enhance your career.
Matt Crabtree's photo

Matt Crabtree

8 min

A Complete Guide to Alteryx Certifications

Advance your career with our Alteryx certification guide. Learn key strategies, tips, and resources to excel in data science.
Matt Crabtree's photo

Matt Crabtree

9 min

PostgreSQL vs. MySQL: Choosing the Right Database for Your Project

Explore the key differences and similarities between PostgreSQL and MySQL to find the best database solution for your project's needs.
Jake Roach's photo

Jake Roach

8 min

SQL Developer Salaries: Expectations in 2024

In this article, we're going to learn what an SQL developer does, what factors impact SQL developer salaries, how the average SQL developer salary compares to the average salaries of other developer profiles, and how you can increase your salary as an SQL developer.
Elena Kosourova's photo

Elena Kosourova

7 min

SQL CONTAINS: A Comprehensive Tutorial

Unlock the power of SQL CONTAINS for advanced text searches. Dive into logical operators, proximity searches, and wildcard uses for precise data analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

See MoreSee More