The INSERT Statement

Part of the You Can Learn SQL series.
By Ken Brown
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.

SELECT statement showing no information in the table.
Image 1 shows a SELECT statement that returns no information from our table.

INSERT statement adding a row to the table.
Image 2 shows the INSERT statement that will add a row to the table.

SELECT statement showing rows added 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

Home| About Us | NewsLetters | Contact Us |

Copyright © 2004-2014 You Can Learn Series

You Can Learn Series Home Page You Can Learn Series
You Can Learn Series Home Page Home            About the creators of YouCanLearnSeriesAbout Us      Contact YouCanLearnSeriesContact Us      Site map of YouCanLearnSeriesSite Map      Privacy Policy of You Can Learn SeriesPrivacy Policy       Terms of Use of You Can Learn Series web siteTerms of Use       YouCanLearnSeries RSS feed for SQL tips and tutorials     
C# and ASP.Net Programming TipsC# and ASP.Net      SQL Tutorial, tips and tricks for DBA'sSQL       Landscaping Tips for Home GardenersLandscaping       WeightLifting Tips for the Young and OldWeightLifting       Good health TipsGoodHealth       Learn about Great Travel DestinationsTravel     Web Business TipsWeb Business     

You Can Learn C# and ASP.Net SeriesC# and ASP.Net

You Can Learn SQL SeriesSQL
  Data Definition LanguageData Definition Language
  Data Manipulation LanguageData Manipulation Language
  What is a DatabaseWhat is a Database
  What is SQLWhat is SQL
  SELECT StatementSELECT Statement
  INSERT StatementINSERT Statement
  DELETE StatementDELETE Statement
  UPDATE StatementUPDATE Statement

You Can Learn Landscaping and Gardening SeriesLandscape Tips
You Can Learn WeightLifting SeriesWeightLift Tips
You Can Learn Good Health SeriesGood Health Tips
You Can Learn Series NewslettersNewsletters
Learn about Great Travel DestinationsTravel
You Can Learn Series Web Business TipsWeb Business Tips

Bill Welter - The Prepared Mind of a Leader

Americas Public Schools