Basic about INSERT Statement – using and example

This SQL tutorial explains how to use the SQL INSERT statement with syntax, examples, and exercise exercises.

Description

The SQL INSERT announcement is used to insert a one or more documents into a table. There are two syntaxes for the INSERT statement relying on whether or not you are inserting one record or more than one records.

Subscribe

Syntax

The syntax for the INSERT announcement when inserting a single file in SQL is:

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... );

Or the syntax for the INSERT assertion when inserting a couple of data in SQL is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_tables
[WHERE conditions];

Parameters or Arguments

table The desk in which to insert the records. column1, column2 These are the columns in the desk to insert values. expression1, expression2 These are the values to assign to the columns in the table. So column1 would be assigned the fee of expression1, column2 would be assigned the fee of expression2, and so on. source_tables Used when inserting information from some other table. This is the source table when performing the insert. WHERE conditions Optional. Used when inserting documents from another table. These are the conditions that should be met for the documents to be inserted.

Note

When inserting information into a table the usage of the SQL INSERT statement, you need to provide a cost for every NOT NULL column. You can solely omit a column from the INSERT statement if the column permits NULL values.

DDL/DML for Examples

If you favor to comply with alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then strive the examples in your very own database!

Get DDL/DML

Example – Using INSERT Statement to Insert One Record

The easiest way use the INSERT assertion is to insert one file into a table the use of the VALUES keyword. Let’s seem at an example of how to do this in SQL.

In this example, we have a table referred to as classes with the following data:

category_id category_name 25 Deli 50 Produce 75 Bakery 100 General Merchandise 125 Technology

Let’s insert a new category record. Enter the following SQL statement:

Try It

INSERT INTO categories
(category_id, category_name)
VALUES
(150, 'Miscellaneous');

There will be 1 file inserted. Select the facts from the classes table again:

SELECT * FROM categories;

These are the results that you should see:

category_id category_name 25 Deli 50 Produce 75 Bakery 100 General Merchandise 125 Technology 150 Miscellaneous

This example would insert one file into the classes table. This new record would have a category_id of 150 and a category_name of ‘Miscellaneous’.

In this example, because you are supplying values for all of the columns in the classes table, you should pass over the column names and alternatively write the INSERT statement like this:

Try It

INSERT INTO categories
VALUES
(150, 'Miscellaneous');

However, this is risky to do for 2 reasons. First, the INSERT declaration will error if additional columns are introduced to the classes table. Second, the information will be inserted into the incorrect columns if the order of the columns exchange in the table. So as a established rule, it is better to list the column names in the INSERT statement.

Example – Using INSERT Statement to Insert Multiple Records

By setting a SELECT announcement inside the INSERT statement, you can perform multiples inserts quickly. Let’s look at an example of how to do this.

In this example, we have a desk called personnel with the following data:

employee_number last_name first_name earnings dept_id 1001 Smith John 62000 500 1002 Anderson Jane 57500 500 1003 Everest Brad 71000 501 1004 Horvath Jack 42000 501

And a table called customers with the following data:

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com

Now, let’s insert some of the employee records into the customers table:

Try It

INSERT INTO customers
(customer_id, last_name, first_name)
SELECT employee_number AS customer_id, last_name, first_name
FROM employees
WHERE employee_number < 1003;

TIP: With this kind of INSERT, some databases require you to alias the column names in the SELECT to suit the column names of the table you are inserting into. As you can see in the example above, we have aliased the first column in the SELECT declaration to customer_id.

There will be 2 files inserted. Select the information from the customers desk again:

SELECT * FROM customers;

These are the results that you should see:

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com 1001 Smith John NULL 1002 Anderson Jane NULL

In this example, the ultimate 2 information in the clients table have been inserted the usage of data from the employees table.

With this kind of insert, you might also want to test for the number of rows being inserted. You can decide the number of rows that will be inserted by using jogging a COUNT(*) on the SELECT statement before performing the insert. For example:

Try It

SELECT COUNT(*)
FROM employees
WHERE employee_number < 1003;

This will return range of information that will be inserted when you execute the INSERT statement.

COUNT(*)

2

Frequently Asked Questions

Question: I am setting up a database with clients. I recognize that you use the SQL INSERT announcement to insert facts in the database, but how do I make sure that I do no longer enter the same patron facts again?

Answer: You can make positive that you do not insert duplicate data by the use of the SQL EXISTS condition.

For example, if you had a desk named clients with a major key of client_id, you ought to use the following SQL INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id AS client_id, supplier_name AS client_name, 'advertising' AS client_type
FROM suppliers
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = suppliers.supplier_id);

This SQL INSERT declaration inserts multiple documents with a subselect.

If you desired to insert a single record, you could use the following SQL INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = 10345);

The use of the dual table lets in you to enter your values in a select statement, even though the values are no longer currently stored in a table.

Practice Exercises

If you prefer to take a look at your skills the usage of the SQL INSERT statement, attempt some of our practice exercises.

These workouts permit you to strive out your skills with the INSERT statement. You will be given questions that you need to solve. After each exercise, we supply the solution so you can take a look at your answer. Give it a try!

Go to Practice Exercises