Database administrators use the SQL language to communicate with relational databases. Some common relational databases include Microsoft SQL Server, Oracle, PostgreSQL, and MySQL. While many of the details of using SQL on a relational database may vary between vendors, the basics of SQL syntax are essentially the same. Using SQL, you can create databases, store data in them, update existing data, and query the data. You can use the output to generate reports or to populate a website.

Database administrators use data manipulation language (DML), part of the SQL language, when they must change data or tables in a relational database. The three basic functions of DML are adding new data, updating existing data, and deleting data. You can use DML statements with other SQL statements when you write queries. In this article, we will look at one of the three common DML statements, INSERT INTO.

The Complete SQL Bootcamp 2021: Go from Zero to Hero

Last Updated October 2020

Bestseller
  • 83 lectures
  • All Levels
4.7 (98,604)

Become an expert at SQL! | By Jose Portilla

Explore Course

The SQL INSERT INTO statement

In SQL, an INSERT INTO statement is used to insert or add one record or row to a table in a relational database.

Basic syntax of an SQL Insert INTO statement is:

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

The columns we plan to insert in the table appear in the VALUES list. It’s important to enclose those inserted values within single quotation marks for character and date/time data types. Single quotations are not necessary for numeric data types or NULL values. A value should be present for each column, and the values should be in the same order as the columns that will appear in the table.

SQL INSERT INTO examples

This is the basic version of the INSERT INTO statement, and it will allow you to add one record to your database table. For the rest of the example queries, we are going to use the famous NorthWinds sample database.

Here is what the records in the Customers table look like:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico

Here are example records in the Suppliers table:

SupplierIDSupplierNameContactNameAddressCityPostal CodeCountry
1Exotic LiquidCharlotte Cooper49 Gilbert St.LondonEC1 4SDUK
2New Orleans Cajun DelightsShelley BurkeP.O. Box 78934New Orleans70117USA
3Grandma Kelly’s HomesteadRegina Murphy707 Oxford Rd.Ann Arbor48104USA

So if we wanted to add a record to the Customer’s table, it would look like this, assuming the CustomerID is a primary key that is auto-incremented by the database:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA');

That is a query you could use, but it is more verbose than we need for this case. Since the CustomerID column is an autoincrement field, we have every value we need to complete an entire row in the table. Because of this, we do not have to specify the columns we are using, as long as the values are in the same order of the columns. So we could replace the query above with this:

INSERT INTO Customers VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA');

When inserting partial sets of columns, however, the columns have to be specified, like this query:

INSERT INTO Customers (CustomerName, City, PostalCode, Country)
VALUES ('Bob Smith', 'Kansas City', '64115', 'USA');

But these examples just scratch the surface of what you can do with INSERT INTO. Let’s look at a few more.

Using INSERT INTO with multiple records

We just looked at how you can add one record to a database table using INSERT INTO. At one time, that was all you could do with it. If you wanted to insert multiple records into the table using it with the VALUES statement, you would have to write multiple queries.

The ability to do multi-row inserts appeared in the SQL language standard in 1992. Most relational databases now support this functionality, but you could run into some that don’t. Microsoft SQL Server didn’t add the ability to do this until 2008.

The syntax for adding multiple records in the VALUES list is what you would expect from SQL. Below is how we would enter multiple customers into the Customer table:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA'),
('Richard Smith', 'Joe Smith', '123 Weber St', 'Kansas City', '64105', 'USA'),
('John Smith', 'Joe Smith', '333 Washington St', 'Independence', '64055', 'USA');

With most of the relational databases that support this, you can also use the shorthand version since the values match the columns in the table.

INSERT INTO Customers VALUES
('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA'),
('Richard Smith', 'Joe Smith', '123 Weber St', 'Kansas City', '64105', 'USA'),
('John Smith', 'Joe Smith', '333 Washington St', 'Independence', '64055', 'USA');

This type of query will not work in MS SQL. For that database, you will have to use the following query:

INSERT INTO Customers
SELECT 'Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA'
UNION ALL
SELECT 'Richard Smith', 'Joe Smith', '123 Weber St', 'Kansas City', '64105', 'USA'
UNION ALL
SELECT 'John Smith', 'Joe Smith', '333 Washington St', 'Independence', '64055', 'USA'
For Oracle, you have to use a DUAL table, like this query:
INSERT INTO Customers
SELECT 'Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA' FROM DUAL
UNION ALL
SELECT 'Richard Smith', 'Joe Smith', '123 Weber St', 'Kansas City', '64105', 'USA' FROM DUAL
UNION ALL
SELECT 'John Smith', 'Joe Smith', '333 Washington St', 'Independence', '64055', 'USA' FROM DUAL

The SQL SELECT statement

The SELECT statement represents data query language (DQL) in SQL. After populating a table with the INSERT INTO statement, we can use SELECT in conjunction with the FROM clause to extract data from the database in an organized, readable manner. The SELECT keyword in a query is followed by a list of columns that you want to display as a result of a query output.

Apart from the keyword FROM, a SELECT statement can be followed by other keywords such as WHERE and ORDER BY to impose conditions with the SELECT statement.

Syntax for a SELECT Statement:

SELECT [column 1, column 2, …] FROM source_table

If we want to copy all columns into the new table, then we use ‘*’ after the SELECT statement.

Syntax:

SELECT * INTO newtable FROM table1;
SQL SELECT INTO example to create a copy of the Customers table:

Syntax:

SELECT * INTO CustomersCopy FROM Customers;

If we want to copy specific columns names into the new table, then we enter:

Syntax:

SELECT [CustomerID, CustomerName] INTO PartialCustomers FROM Customers;

Here, it will create a new table with the column names and types as defined in the SELECT statement.

Using the INSERT INTO SELECT statement

Now we will look at how we can use what we just learned to write even more powerful queries. A combination of INSERT INTO and SELECT statements helps you to insert multiple rows and data into a table based on the results of a query from another table. A query is a question that the user asks the database, and the data returned is the answer to the question or query. The combination of INSERT INTO and SELECT statements allows you to conditionally, or unconditionally, insert data into multiple tables. This statement reduces table scans, and you have to write less code to perform multiple conditional inserts.

The basic syntax for inserting multiple records using an INSERT and SELECT statement is:

INSERT INTO table (column1, column2, … ) SELECT expression1, expression2, … FROM source_table WHERE conditions

Explanation of the parameters or arguments used in the above syntax:

When using INSERT…SELECT between a target and a source table, it is mandatory to ensure that the datatypes returned by the SELECT statement are compatible with the datatypes in the target table.

INSERT INTO SELECT examples

Our two NorthWind database tables work well for examples of using INSERT INTO with SELECT because both the Customers table and the Suppliers table have similar columns. Let’s say that we want to make every supplier a customer. You can do this with one query.

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

Notice that we specified the columns. This is because both tables have auto-increment ids, and we want to create new customers. If we used a shorthand query using an asterisk like the one below, we would have run into errors.

SELECT * INTO Customers FROM Suppliers;

One error would be because Suppliers has a SupplierName column and Customers has a CustomerName column. Also, the Id columns of each table would clash.

But let’s say that we just want to only insert suppliers into the Customers table if they were from the United States. Then we would just have to add a WHERE clause to the SELECT statement to filter out just those suppliers. The following query will create new customers in the Customer table out of the suppliers from the United States:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers
WHERE Country = 'USA';

If we only want to insert a partial set of columns, we can do that too. The following query only inserts the name and the country.

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country = 'USA';
If we want to insert records from multiple countries, we can use the IN clause in the query like this:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers
WHERE Country IN ('USA', 'UK');

Retrieving the primary key after using INSERT INTO

Database developers commonly need the primary key of the inserted record after it has been inserted. An example would be creating a customer record on a website that interacts with the Customer table, saving the customer, and then redirecting to the view of the customer record on the website. In this instance, you would click save on the website. This would result in a query like this:

INSERT INTO Customers VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA');

Then we need the CustomerId after this query to redirect users to the customer record. The problem is that this query won’t return anything other than a message stating how many records were inserted in most relational databases, which in this case is one. Now, what we could do is run a query like this right afterword:

SELECT CustomerId FROM Customers WHERE CustomerName = 'Bob Smith' AND ContactName = 'Joe Smith' AND Address = '111 Main St' AND City = 'Kansas City' AND PostalCode = '64115' AND Country = 'USA';

That is a little ridiculous. The query to get the simple CustomerId is more complicated than the original query. Fortunately, with many database engines, there is a way to get an INSERT INTO query to return the id of the record. That way, the web application could just retrieve the CustomerId from the query itself and redirect us immediately without an extra query.

But the syntax for doing this depends on the relational database that you are using. For Oracle, PostgreSQL, and a few others, this query will work to return the CustomerId of the customer record that just got inserted by using the RETURNING clause:

INSERT INTO Customers VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA') RETURNING CustomerId;

You can also rename the CustomerId if you want. The following query returns the same id as an ID column:

INSERT INTO Customers VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA') RETURNING CustomerId INTO ID;

For Microsoft SQL Server, this is a little different. You will have to make a separate query to get the CustomerId, but at least it is just a function you call and not the complicated SELECT query above. Here is what you would have to write in Microsoft SQL Server:

INSERT INTO Customers VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA');
SELECT SCOPE_IDENTITY();

For MySQL, you also have to use a separate query. Like SQL Server, it is pretty simple to use. Here is the same query in MySQL:

INSERT INTO Customers VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA');
SELECT LAST_INSERT_ID();

And if you are still using MS SQL, here is the query you have to write:

INSERT INTO Customers VALUES ('Bob Smith', 'Joe Smith', '111 Main St', 'Kansas City', '64115', 'USA');
OUTPUT INSERTED.ID;

So while using the INSERT INTO statement works just about the same for most database engines, if you want the primary key of the record you inserted, the query will vary widely depending on the relational database you are using.

Top courses in Development

The Complete 2021 Flutter Development Bootcamp with Dart
Dr. Angela Yu
4.7 (31,457)
Bestseller
Understanding TypeScript – 2021 Edition
Maximilian Schwarzmüller
4.7 (21,391)
Bestseller
The Data Science Course 2021: Complete Data Science Bootcamp
365 Careers, 365 Careers Team
4.6 (91,361)
Bestseller
Unreal Engine C++ Developer: Learn C++ and Make Video Games
Ben Tristem, Sam Pattuzzi, GameDev.tv Team, Rob Brooks
4.6 (51,502)
Bestseller

More Development Courses

Triggers with INSERT INTO

A trigger is a procedure that automatically executes when an event happens in a database. You can create DML triggers on a database when INSERT, UPDATE, or DELETE statements run. These triggers will occur whenever an event happens, whether it resulted in a change in the database or not.

Adding a trigger to an INSERT event is a common task in a database. In our example Customer table, we might want some things to occur when we get a new customer. Maybe we need to add a record to a table that assigns the customer to a representative or another table that holds a mailing list for new customer information packets.

With INSERT triggers, you have the option of BEFORE INSERT triggers or AFTER INSERT triggers. BEFORE INSERT triggers allow you to modify the data before it gets added to the table, and AFTER INSERT triggers cannot change the inserted data. Still, they would work to handle the actions described above that we want to happen after we insert a new customer.

Conclusion

The SQL INSERT INTO statement is something you will use all the time in your SQL queries. There are many ways you can use this query, from simply inserting records into the table to copying whole tables to copying parts of one table into another table.

As a whole, SQL is a simple, friendly, English-like language that is relatively easy to learn. It’s also quite accessible since database users and developers all over the world use it extensively. To be a successful database administrator, you have to know how to structure and develop a properly designed and linked database, which will help you retrieve information from as many tables as you want. A good place to start is becoming familiar with essential SQL commands that you will use every day. 

Let’s move on to learning the SQL update command! 

Top courses in SQL

SQL and PostgreSQL: The Complete Developer's Guide
Stephen Grider
4.8 (1,416)
Highest Rated
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (54,069)
Bestseller
Advanced SQL : SQL Expert Certification Preparation Course
Oracle Master Training • 80,000+ Students Worldwide, Code Star Academy
4.4 (2,176)
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jon Avis - SQL Instructor
4.5 (10,846)
SQL & PostgreSQL for Beginners: Become an SQL Expert
Jon Avis - SQL Instructor
4.5 (3,195)
Bestseller
SQL Programming Basics
Global Academy
4.3 (95)
Oracle SQL - Step by Step SQL
Amarnath Reddy
4.4 (2,159)

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo