This SQL tutorial explains how to use the SQL INSERT statement with syntax, examples, and exercise exercises.
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.
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.
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!
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:
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:
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:
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:
SELECT COUNT(*) FROM employees WHERE employee_number < 1003;
This will return range of information that will be inserted when you execute the INSERT statement.
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.
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