Part of the
You Can Learn SQL series.
By Ken Brown
Editor, YouCanLearnSeries.com
Updated: October 29, 2004
This is a two part article on the INSERT Statement
INSERT Statement, Simple Inserts and Basic Inserts.
INSERT Statement, Using a SELECT statement to insert values.
The SQL INSERT statement is used to add rows to a table or view. When you want
to add information to a table you use the INSERT keyword. This is different
from the UPDATE statement because when using the UPDATE statement you are
changing information in a row. With the INSERT statement you are adding new
information to the table.
As an example, if you had a table that contains name, address, city, state and
zip information and you wanted to add your cousin, Jim's information to the
table and Jim's name and address information wasn't already in the table, then
you would use an INSERT statement. If Jim had an existing record in the
database and you only wanted to change his zip code, then you use an UPDATE
statement.
1. Basic INSERT statement
The format for the INSERT statement begins like this:
Here you are inserting information into a table that contains two columns
named, Col1, Col2. You will place the string "column 1 value" into column named
col1 and "column 2 value" into column named col2.
The following example shows a real life table where you are doing an insert of
the following fields, friendName, address, city, state and zip. It shows a
picture before you insert the row and then after you insert the new row.
Image 1 shows a SELECT statement that returns no information from our table.
Image 2 shows the INSERT statement that will add a row to the table.
Image 3 shows the name and address added to the table.
2. Simple INSERT statement
You can simplify the INSERT statement by removing the word INTO. If the INTO
keyword is not present then it is implied. To simplify the statement even
further, if you are populating all the columns in the table, then you can
remove all reference to the columns. So our "simplified" INSERT looks like
this:
This produces the exact same result as the statement in item one.
3. Changing the order of information being inserted
You don't have to keep the columns in the same order as they are in the
database table. When you create your INSERT statement whatever order the
columns are listed in the statement will be okay, as long as you define the
columns before you list the values. If you don't define the columns and use
only the simplified version, then you must populate all columns in the table
and they must be in the exact same order as defined for the table. It is okay
to list your statement:
If you make a mistake and place your "column 1 value" in the first column, then
SQL server will update your table with the improper information in col2.
Go To Page 1 / 2
|