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:

INSERT INTO BasicTable 
(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)

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.

Here is an example of a statement that will not work:

INSERT INTO BasicTable 
values ("Byron", "Janis")

The engine will tell you that you cannot insert the information because it does not match the format of the table.
Here is an example of the error message you will receive from SQL Server:

Msg 213, Level 16, State 4
Insert error: column name or number of supplied
values does not match table definition.

You have three columns in the table, but only two data elements were provided. Either you need to tell the engine where the information needs to be placed by indicating the column names, or you need to provide the same number and order of data elements so that they match your database table.

INSERT INTO myLibrary VALUES (‘SQL Bible by Alex Kriegel Boris M. Trukhnov
Paperback: 888 pages Publisher: Wiley; 2 edition (April 7,2008)
Language:English ISBN-13: 978-0470229064’);

The keywords INSERT, INTO, and VALUES are the elements of the SQL language and together instruct the RDBMS to place the character data (in the parentheses, surrounded by single quotation marks) into the database table. Note that we did not indicate the column name; first because we have but a single column in which to insert, and second because RDBMS is smart enough to figure out what data goes where by matching a list of values to the implied list of columns. Both parentheses and quotation marks are absolutely necessary: the former signifies a list of data to be inserted, and the latter tells the RDBMS that it is dealing with text (character data type). In database parlance, we have created a record in the table. There are many more books on thevshelf, so how do we enter them? One way would be to add all of them on the same line, creating avhuge single record. Although that is possible, within limits, it would be impractical, creating a pilevof data not unlike the refrigerator model we discussed earlier: easy to add and difficult to find. Do I hear "multiple records"?
Answer: Yes
The previous statement could be repeated multiple times with different data until all books are entered into the table; creating a new record every time. Instead of a refrigerator model with all data all in one place, we moved onto “chest drawer model” with every book having a record of its own.

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.