---[REF]---"style":"heading"---[REF]--- ---[SIDEBAR]--- "type":"objectives", "title":"Learning Objectives", "text":"After completing this lesson, the successful student will be able to use SQL INSERT statements to add data to a database table. Specifically:

1. Add a complete row to a table.
2. Add a partial row to a table.
3. Add query results as multiple rows to a table.

" ---[SIDEBAR]--- The typical user spends the overwhelming majority of their time retrieving data from database tables. In fact, it is possible that you will only retrieve data from database tables. This is the primary reason why most of the lessons in this resource are devoted SELECT statements. SQL can do more than just retrieve data. ---[REF]---"style":"link","pageId":"lesson-creating-tables"---[REF]--- described how to use SQL statements to create database tables. SQL can also be used to add, update, and delete rows from database tables as well. ---[REF]---"style":"link","pageId":"lesson-update-delete"---[REF]--- will discuss how to update and delete data. This lesson covers how to add data to database tables. Data is added to a table using the keyword INSERT. Data can be inserted into a table in a few ways. You can add INSERT: ---[SIDEBAR]--- "type":"term", "title":"INSERT", "text":"Used in SQL statements to add data to a table." ---[SIDEBAR]--- * A complete row of data * A partial row of data * The results of query Each of these methods will be discussed in this lesson. ---[SIDEBAR]--- "type":"note", "title":"SQLite and Changing Data", "text":"If you are using the SQLite option for this lesson, the changes you make to the data will persist until you refresh or leave this page." ---[SIDEBAR]--- ---[SIDEBAR]--- "type":"tip", "title":"Inserting Data", "text":"Since actions such as inserting, updating, and deleting data make permanent changes to the database, the database administrator will configure the database to only allow certain users to perform these actions." ---[SIDEBAR]--- ## Inserting Complete Rows The easiest way to add data to a table is one complete row at a time. This keywords INSERT, INTO, and VALUES are used for this method. The syntax is: INSERT INTO *tablename* VALUES (*comma separated list of values for each column*) The keyword INSERT signals the DBMS that data will be added. The keyword INTO specified where the data will be added. The name of the table where the data will be added follows INTO. The keyword VALUES details the values to be added in the new row of the table. The values are listed in parentheses using a comma separated list. The values must be listed in the order that the columns appear in the database table. A value must also be included for each column. If there isn't a value to be added for a column, NULL must be entered for that column. ---[SIDEBAR]--- "type":"note", "title":"Inserting Data", "text":"The values added must match the data type for the column. For example, text values must be enclosed in quotes." ---[SIDEBAR]--- Suppose you want to add a new museum to the museum table in the database. The details of the new museum are on the table below:

| Column | Value | |:---------:|--------------------------------------------------------------------------| | museum_id | 100 | | name | Utah Museum of Fine Arts | | address | 410 Campus Center Drive | | city | Salt Lake City | | state | Utah | | postal | 84112 | | country | USA | | phone | +1 801 581-7332 | | url | https://umfa.utah.edu |

The syntax for adding the new museum is: INSERT INTO museum VALUES (100, 'Utah Museum of Fine Arts', '410 Campus Center Drive', 'Salt Lake City', 'Utah', 84112, 'USA', '+1 801 581-7332', 'https://umfa.utah.edu') **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-insert-umofa ---[SQL]--- INSERT INTO museum VALUES (100, 'Utah Museum of Fine Arts', '410 Campus Center Drive', 'Salt Lake City', 'Utah', 84112, 'USA', '+1 801 581-7332', 'https://umfa.utah.edu'); SELECT * from museum ORDER BY museum_id DESC; ---[SQL]--- Because the INSERT query does not retrieve records, not records will be displayed when executing the query. Notice that a second query has been added to the query window to show that the new row has been added to the table. Although this syntax is relatively simple, it is not the best way to add a row to a database table. At best this approach is inflexible. It relies on the user adding a value for each column in the table and adding the values in the exact order that the columns appear in the table. Unfortunately, the user can't always depend on the columns being stored in the order they appear to be in the database. This means that this statement may not reliably work. A more flexible and reliable method for adding a row to a database table involves specifying the columns that will receive values in the new row. This syntax is: INSERT INTO museum (museum_id, name, address, city, state, postal, country, phone, url) VALUES (100, 'Utah Museum of Fine Arts', '410 Campus Center Drive', 'Salt Lake City', 'Utah', 84112, 'USA', '+1 801 581-7332', 'https://umfa.utah.edu') Notice the comma separated list of column names in parentheses between the tablename and the keyword VALUES. The list of column names specifies which columns will receive the corresponding values in the list of values. The first column (museum_id) will receive the first value (100). Each of the other columns will receive the other values in the order in which the columns are specified. The syntax below will also insert the same row of data. Notice that the order of the museum_id and name have been reversed. As long as the values are listed in the appropriate order for the names of the columns, the data will be inserted correctly. INSERT INTO museum (name, museum_id, address, city, state, postal, country, phone, url) VALUES ('Utah Museum of Fine Arts', 100, '410 Campus Center Drive', 'Salt Lake City', 'Utah', 84112, 'USA', '+1 801 581-7332', 'https://umfa.utah.edu') ---[SIDEBAR]--- "type":"note", "title":"Inserting Data by Specifying Column Names", "text":"When you specify the columns names in the INSERT INTO statement, you may list the columns in any order." ---[SIDEBAR]--- **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-insert-umofa ---[SQL]--- INSERT INTO museum (museum_id, name, address, city, state, postal, country, phone, url) VALUES (100, 'Utah Museum of Fine Arts', '410 Campus Center Drive', 'Salt Lake City', 'Utah', 84112, 'USA', '+1 801 581-7332', 'https://umfa.utah.edu'); SELECT * from museum ORDER BY museum_id DESC; ---[SQL]--- ---[SIDEBAR]--- "type":"tip", "title":"Always Provide Column Names In INSERT Statements", "text":"Although it is more cumbersome, it is a best practice to always provide the column names when you create an INSERT INTO statement." ---[SIDEBAR]--- ## Inserting Partial Rows One additional benefit to providing the column names in the insert statement is that you may not need to add a value for each column when inserting a new row to the table. You will only need to provide a value for the columns you specify. Columns omited from the list will not receive a value. For example, suppose you want to add the Weisman Art Museum to the museum table, but you don't have all of the information about the museum. You know the name, city, state, country, and URL.

| Column | Value | |:---------:|--------------------------------------------------------------------------| | museum_id | 101 | | name | Weisman Art Museum | | city | Minneapolis | | state | Minnesota | | country | USA | | url | https://wam.umn.edu |

The INSERT statement needed to add the new museum is: INSERT INTO museum (museum_id, name, city, state, country, url) VALUES (101, 'Weisman Art Museum', 'Minneapolis', 'Minnesota', 'USA', 'https://wam.umn.edu'); **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-insert-wam ---[SQL]--- INSERT INTO museum (museum_id, name, city, state, country, url) VALUES (101, 'Weisman Art Museum', 'Minneapolis', 'Minnesota', 'USA', 'https://wam.umn.edu'); SELECT * from museum ORDER BY museum_id DESC; ---[SQL]--- Notice that when adding the Weisman Art Museum with missing columns and values, the columns that do not receieve values have NULL values. Of course, this will only work if those columns can have NULL values (recall from ---[REF]---"style":"link","pageId":"lesson-creating-tables"---[REF]--- that a column can be constrained to not allow NULL values.) As long as a column can receive a NULL value, a new row can be added to a table without a value for that column. If a column that is set to not allow NULL values is omitted from those provided in the INSERT INTO statement, the DBMS will return an error and the new row will not be added to the table. Having the flexibility to insert incomplete rows can be very helpful. Often, you may not know the values for some columns when the row is initially added to the table. Care must be taken to complete the row when the missing values are know. ## Inserting Retrieved Data You can also add data to a table more than one row at a time. This is done by adding the results of a query that retrieves data to a different table in the database. There are two approaches to doing this. The first method creates a new table in the database The new table contains the columns and rows that are generated from a query result. The second involves adding the results of a query to an exiting table. Both approaches are discussed in this section. ### Adding Query Results to a New Table To add the results of a query to an a new table, you will combination of the SQL statements used for creating a new table and retrieving data. The general syntax for this method is: CREATE TABLE *tablename* AS SELECT *...the rest of a valid SELECT query* The results of the SELECT portion of this statement will be used to create a new table with supplied name. This new table will contain the columns and rows in the query result. The SELECT portion of the statement can use any of the clauses of a typical SELECT query allowing you to precisely retrieve the data you wish to add to the table. Assume you would like to create a new table (named museum_copy) with all information about the museums that are located in the United States. The SQL statement that would accomplish this result is: CREATE TABLE museum_copy AS SELECT * FROM museum WHERE country = 'USA' The select portion of this statement will generate a list containing all of the columns on the museum table for the museums with a country of USA. The CREATE TABLE portion of this query will add a new table named museum_copy to the database. This new table will contain the columns and rows returned by the SELECT portion of the statement. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-create-copy-add-usa-museums ---[SQL]--- CREATE TABLE museum_copy AS SELECT * FROM museum WHERE country = 'USA'; SELECT * from museum_copy; ---[SQL]--- Since the statement does not technically retrieve data, it will not be displayed with executing the query. Notice the second statement that returns all of the data from the new museum_copy table. All of the columns and rows for museums located in the USA are stored on this new table. ---[SIDEBAR]--- "type":"note", "title":"Creating Tables from Query Results", "text":"Creating tables from query results can be a quick way to add a table with data to the database. None of the column and table constraints are defined during this process. For example, the table does not have a column designated as the primary key. You will likely want to alter this table to include column and table constraints if you plan to keep the table permanently." ---[SIDEBAR]--- ---[SIDEBAR]--- "type":"caution", "title":"Creating Tables from Query Results", "text":"This approach both creates a new table and adds data to that new table. If a table with the supplied name already exists in the database, the DBMS will be unable to create the new table and will return an error." ---[SIDEBAR]--- ### Adding Query Results to an Existing Table You can also write a SQL statement to the results of a query to the end of an existing table. This is done using a combination of the SQL statements used to add a row of data to the table and to retrieve data from an existing table. The general statement that accomplished this is: INSERT INTO *tablename* (*columns receiving data*) SELECT ...*the rest of a valid SELECT statement* The INSERT INTO part of this statement is similar to the syntax used to add a complete or partial row of data to a table. After the keywords INSERT INTO, you provide the name of the table that will receive new rows of data. You also provide a list of the columns that will receive the new data. The SELECT portion of this statement will be a valid SELECT statement that will retrieve data from a different table. Care should be taken to ensure that the columns retrieved in the SELECT statement match the columns provided in the INSERT INTO portion of the statement. Again the SELECT portion of the statement can use any of the clauses of a typical SELECT query so you can precisely retrieve the data you wish to add to the table. ---[SIDEBAR]--- "type":"caution", "title":"Add Data to Tables from Query Results", "text":"This approach will add data to an existing table. If a table with the supplied name does not in the database, the DBMS will be unable to insert the new data and will return an error." ---[SIDEBAR]--- Suppose you want to add all of the museums located in France to the museum_copy table you just created. The SQL statement that accomplishes this result is: INSERT INTO museum_copy (museum_id, name, address, city, state, postal, country, phone, url) SELECT * FROM museum WHERE country = 'France'; The select portion of this statement will generate a list containing all of the columns on the museum table for the museums with a country of USA. The CREATE TABLE portion of this query will add a new table named museum_copy to the database. This new table will contain the columns and rows returned by the SELECT portion of the statement. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-add-french-museums ---[SQL]--- INSERT INTO museum_copy (museum_id, name, address, city, state, postal, country, phone, url) SELECT * FROM museum WHERE country = 'France'; SELECT * from museum_copy ORDER BY country; ---[SQL]--- Notice that the museums located in France have been added to the museum_copy table. If the museum_copy table does not already exist in the database, an error message will be returned by the DBMS. ## Summary This lesson described how to use SQL statements to add new rows to database tables. Specifically, it described how to add a complete row to an existing table and a partial row to an existing table. Additionally, it described how to add multiple rows to a database table from the results of a query.