|Part of the
You Can Learn SQL series.
By Ken Brown
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.
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
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
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
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.
Go To Page 1 / 2