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
566.4K
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

Mastering SQL ROUND: Precision Handling in Data Analysis

Learn how to use the SQL ROUND function in this tutorial. It covers the implementation of the ROUND function in Oracle, MySQL, PostgreSQL, and SQL Server.
Laiba Siddiqui's photo

Laiba Siddiqui

10 min

Mastering SQL NOT EQUAL Operator: A Beginner's Guide

Unlock the power of SQL NOT EQUAL with our expert guide. Learn to refine data queries with practical examples and optimization tips for better analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL NOT IN Operator: A Comprehensive Guide for Beginners

Master SQL's NOT IN operator with this beginner's guide. Learn to filter data effectively, avoid common pitfalls, and explore efficient alternatives
Abid Ali Awan's photo

Abid Ali Awan

5 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