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.

4. Insert information with fewer columns than in the table.

Sometimes your table will contain multiple columns. You may not have all the information you need at the time you want to create the row to populate all the columns that are available. That's okay. As long as you define the column or columns you want to populate and provide a value for each column you'll be okay.

Imagine you have a table with four columns, Col1, Col2, COl3, and Col4. Say you have information for two of the four columns. You can successfully insert a new row into the table using the following command.

The only caveat you have to be concerned with is the columns you don't populate must allow NULL values. Otherwise you will receive an error message similar to "NUll values not allowed." and the INSERT will fail. At that point you have to identify what columns failed and provide a value for those columns before you can successfully insert that row.

5. A table with an identity column

Sometimes you will encounter a table that has an identity column. That is a column that is populated with the "next" available number. The SQL server will normally insert the number into the row you create automatically, so you don't have to be concerned with it.

If you have a table with three columns, identity column, col1 and col2. Then you only have to insert values into col1 and col2. The identity column will be populated automatically with the next appropriate value when you execute the INSERT statement.

6. Using a SELECT statement to insert values

Sometimes you want to create new new tables and then populate the new table with values from an older table. If you create a new table called users that contains only name and email address. If you already have a table that contains those fields, then you can load the new table with a SELECT statement from the old table.

Every row in oldtable will be read, the name and email will be extracted and then inserted into the new users table.

There you have six useful ways to use the INSERT statement to add rows of information into your tables.

Happy programming.

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