31 Oct Sql server tutorial 08 Modifying SQL Data
Key Skills & Concepts
- Insert SQL Data
- Update SQL Data
- Delete SQL Data
- Merge SQL Data
One of the primary functions of any database is to be able to manipulate the data stored
within its tables. Designated users must be able to insert, update, and delete data as
necessary in order to keep the database current and ensure that only the appropriate data is
being stored. SQL provides three statements for basic data manipulation: INSERT,
UPDATE, and DELETE. Also, SQL provides the more powerful MERGE statement that
can be used to apply inserts and updates (or deletes) to a table based on the contents of
another table. In this chapter, I will examine each of these statements and demonstrate
how they can be used in an SQL environment to modify data in the database.
Insert SQL Data
In Chapter 7, Try This 7-1, I introduce you briefly to the INSERT statement. As you can
see from that exercise, the INSERT statement allows you to add data to the various tables
in your database. I present the basic syntax in this section and an alternate syntax in the
next section “Inserting Values from a SELECT Statement.” The syntax for a basic
INSERT statement is relatively straightforward:
Only the first and last lines in the syntax are required. The second line is optional. Both
the first and second lines are part of the INSERT INTO clause. In this clause, you must
identify the name of the table (or view) into which you will be inserting data. The table
name follows the INSERT INTO keywords. You then have the option of identifying the
column names in the table that will be receiving the data. This is the purpose of the second
line in the syntax. You can specify one or more columns, all of which must be enclosed in
parentheses. If you specify more than one column, they must be separated using commas.
Most SQL implementations support inserts into views. However, there are restrictions. For example, you cannot
insert into a view if there are table columns that are not included in the view and those columns do not allow null
values and do not have a default value defined. Furthermore, if the view has more than one base table, you may
not be able to insert into it at all, but if you can, you will be required to name columns from only one of the base
tables because an insert can affect only one base table. Always check your vendor documentation.
In the third line of syntax, which is the VALUES clause, you must specify one or more
values that will be inserted into the table. The list of values must be enclosed in
parentheses and, if more than one is specified, they must be separated using commas. In
addition, the values must meet the following requirements:
If the column names are not specified in the INSERT INTO clause, then there must be
one value for each column in the table and the values must be in the same order as
they are defined in the table.
If the column names are specified in the INSERT INTO clause, then there must be
exactly one value per specified column and those values must be in the same order in
which they are defined in the INSERT INTO clause. However, the column names and
values do not have to be in the same order as the columns in the table definition.
You must provide a value for each column in the table except for columns that either
allow null values or have a default value defined.
Each value with a character string data type must be enclosed in single quotes.
You may use the keyword NULL (or null) as the data value in the VALUES clause to
assign a null value to any column that allows nulls.
You may use the keyword DEFAULT (or default) as the data value in the VALUES
clause to assign the default value to any column that has a default value defined.
You may use the keywords NEXT VALUE FOR followed by a sequence generator
name in the VALUES clause to assign the next value from an independent sequence
generator to any column that is defined with a compatible numeric data type. I discuss
sequence generators in the “Maintaining Independent Sequence Generators” section in
Many SQL programmers prefer to specify the column names in the INSERT INTO clause, whether or not it’s
necessary to do so, because it provides a method for documenting which columns are supposed to be receiving
data. This practice also makes the INSERT statement less prone to errors and other problems should columns be
added or the column order be changed at some future time. For these reasons, many organizations require the use
of the column names in all INSERT statements.