SQL Foundations  «Prev  Next»

Lesson 8Using the INSERT statement
ObjectiveHow to Use SQL INSERT Statement to add Information

Use SQL INSERT Statement to populate Database Tables with Data

Now that you understand the basics of working with a table, from creating it to building an index on it, let us take a moment and put something into a table and then query it to see what we can get out. It is a simple process, and you will quickly see how SQL works for you.
To insert information into a table, use the INSERT statement, indicate the table and columns you want to reference, and then provide the information. So, for BasicTable, the following INSERT statement would insert a row:

(Lastname, Firstname) 
values ("Larrry", "Ellison")

The resulting row would have the two columns we provided, and would leave the phone number blank, or null.
Firstname Lastname PhoneNumber
Larry Ellison (null)

Incorrect SQL

The INSERT statement calls out the table name first. The next step is to provide the columns that will be filled. You can omit these from the statement if your data gives information for all columns in the order in which they are defined for the database. Here is example of a statement that will not work.
Note that you can use single or double quotes, depending on the database. Experiment if you are not sure which one works for your application.

Inserting New Data

The INSERT INTO statement makes inserting new data into the database very easy. All you need to do is specify into which table you want to insert data, into which columns to insert data, and finally what data to insert. The basic syntax is as follows:

INSERT INTO table_name (column_names) 
VALUES (data_values)

This line of code adds a record to the Category table:
INSERT INTO Category (CategoryId, Category) 
VALUES (1, ‘Thriller’);

You can see that inserting data is simply a matter of listing each column name (separated by a comma) in the brackets after the table name. In the brackets after the VALUES statement, simply list each item of data to go into the matching column and separate each with a comma. Character and date data must be wrapped up inside single quotes. Delimiters are unnecessary around numerical data; simply insert them as is. If you load the SQL editor of your RDBMS, connect to your Film Club database, and then enter and execute the statement, the following record is added to the Category table: To check whether it worked, either use your RDBMS’s management tools to view table data or use the following SQL statement:

SELECT * FROM Category

This statement displays all the records in the Category table. For now, just use it to ensure that the INSERT INTO statement worked. Once you make sure the first INSERT statement works, you can insert more values into your Category table:
INSERT INTO Category (CategoryId, Category) VALUES (2, 'Romance');
INSERT INTO Category (CategoryId, Category) VALUES (3, 'Horror');
INSERT INTO Category (CategoryId, Category) VALUES (4, 'War');
INSERT INTO Category (CategoryId, Category) VALUES (5, 'Sci-fi');
In the next lesson, you will learn how to get your inserted information out of the database.
Ad SQL Database Programming