INSERT adds new records to your tables. You can add static values, values from a stored procedure, or even values from another table. The INSERT statement is much more flexible than the UPDATE statement, but SQL does have some limitations. 

Just like other SQL statements, if you attempt to add a value that doesn't match the data type set in the table design, SQL throws an error. You must know your table design to insert new records into the table. For instance, if you try to insert "Tom" into the column named "CustomerId" that's designated as an integer column, SQL gives you an error and the INSERT statement fails. 

First, when you're creating your INSERT statements, you need a template. The following code is the INSERT template: 

INSERT INTO <table>

(column1, column2) VALUES (value1, value2) 

INSERT INTO is the phrase used to add a record into your table. The "<table>" can be any table in your database. The table must be spelled properly or SQL gives you an error. 

The second section of the INSERT statement is the meat of the query. The first set of parenthesis defines the columns that you'll populate when the query runs. The second set of parenthesis defines the values. You must have the same number of values as you have columns. For instance, if you have 3 columns listed in the first set of parenthesis and 4 values in the second set of parenthesis, SQL gives you an error. 

The data type rules also apply. For instance, suppose column1 in the first set of parenthesis is defined as an integer in your table but value 1 is set to "Tom," SQL throws an error. In this chapter, we'll carry over the table from Chapter 3. The following structure is the Customer table: 

CustomerId	First_name	Last_name	City	State
123	Tim	Smart	Miami	FL
321	Frank	Doe	Dallas	TX

With the INSERT statement, the structure in the table design is important. CustomerId is an integer. First_name, Last_name, City and State are set as varchar or string values. The following INSERT statement adds one record to the table: 

INSERT INTO Customer

(CustomerId, First_name, Last_name, City, State) VALUES (455, ‘Ed', ‘Thompson', ‘Atlanta', ‘GA') 

Notice the number of columns defined in the first set of parenthesis is the same number of values defined in the second set of parenthesis. The result is a new record in your table, so now your table looks like the following data set: 

CustomerId	First_name	Last_name	City	State
123	Tim	Smart	Miami	FL
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA

The issue is that our table uses a primary key for CustomerId. When you use a primary key column, the field's value for each record must be unique from the rest. What if you accidentally insert a duplicate primary key? SQL throws you an error when you try to insert duplicate primary key values. When the INSERT statement fails, nothing is inserted into the table, so you add bugs to your application. The user receives an error and can't sign up on your website. 

The way to deal with the issue is to auto-assign column values. With a primary key column, the database administrator assigns an auto-increment function to the column. The auto-increment column adds 1 to the last primary key value. For instance, if the last value inserted into your table was "200," the auto-increment function automatically assigns "201" to the next record. You don't set a value for the primary key column and skip it in your INSERT statement. 

Assuming the primary key column CustomerId has the auto-increment function assigned, you can then use the following SQL statement to insert records into your Customer table: 

INSERT INTO Customer

(First_name, Last_name, City, State) VALUES (‘Ed', ‘Thompson', ‘Atlanta', ‘GA') 

Notice that the CustomerId column is removed from the SQL statement, and it's also deleted from the list of values. This INSERT statement assumes that the CustomerId column is pre-populated with a value. The administrator can pre-populate any value in a table, so it's important to know your table design before you set off to create SQL statements. Using auto-increment, you avoid accidentally inserting a duplicate value and you don't need to keep track of the last value inserted into the table. The database does it all for you. 

If you used the query above, your table would then look like this: 

CustomerId	First_name	Last_name	City	State
123	Tim	Smart	Miami	FL
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA

Notice that the last record has an ID of 456. The auto-incremented column took the last ID inserted and added 1. Then, it used this value for your primary key. No code is needed from you. 

Sometimes, you need to import values from one table into another. You can combine an INSERT statement with a SELECT statement to add records to your tables from other tables. Suppose you have a table you imported from a file. The file contains a list of customers you want to add to your customer table. You have the right information you need including First_name, Last_name, City and State. The following SQL statement imports data from a table named NewCustomers to the current Customer table: 

INSERT INTO Customer

Select First_name, Last_name, City, State FROM NewCustomer 

Notice that the CustomerId column is excluded. Just like manually inserting a primary key value, you don't want to use Ids from the NewCustomer table. The second table could have duplicate customer IDs, and the result is that your INSERT statement will fail. The above SQL statement assumes that CustomerId is an auto-incrementing field, so the SQL server takes care of the ID values. 

For instance, the following table is an example of a table with customer data. 

First_name	Last_name	City	State	Phone
Joe	Smith	Miami	FL	999-999-9999
Frank	Doe	Dallas	TX	874-584-5555

Notice that the above table has a phone number column. The original Customer table has no Phone column, so you can't insert it into the table. SQL will only insert the columns you specify in your SELECT statement, so the Phone column values are completely ignored. The secondary table is still intact, so you have the data but you don't move the column to your original Customer table. 

And then your Customer table would now look like the following. 

CustomerId	First_name	Last_name	City	State
123	Tim	Smart	Miami	FL
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

Finally, your INSERT statements can use NULL if you don't know the values for certain columns. For instance, suppose you only have a first and last name for a customer, but you don't have the city and state. You can use the NULL value to add something to the column as a placeholder until you get the real information from the customer. The following statement uses NULL values for city and state. 

INSERT INTO Customer

(First_name, Last_name, City, State) VALUES (‘Ed', ‘Thompson', NULL, NULL) 

The INSERT statement is the standard way you add data to your tables. The INSERT statement is usually the easiest to learn because it doesn't have as many options as the other SQL statements. 

Your table would then look like the following data set. 

CustomerId	First_name	Last_name	City	State
123	Tim	Smart	Miami	FL
321	Frank	Doe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX
459	Ed	Thompson	Atlanta	GA