SQL 2005

Basic SQL Server Data Manipulation

The seventh part of the SQL Server Programming Fundamentals tutorial uses the tutorial database created in the previous articles to take a first look at data manipulation. In this article we will create, update and delete information in SQL Server tables.

JoBS Database Scripts

In the first six instalments of this tutorial we have created a SQL Server database named "JoBS". This database gives an example of the data storage that may be required for a simple, real-world job processing application.

If you have not followed the previous articles in the tutorial, you can download a script to create the tutorial database using the link at the top of this page. Executing the "CreateJoBS.sql" script will create a new, empty database. If you already have the database, you will not need to follow this step. However, you may decide to do so if you have modified the JoBS database and wish to refresh it.

Creating Data

As the JoBS tutorial database currently holds no data, the first task is to generate some. As with previous SQL Server processes that have been described in this tutorial, we can add data using SQL Server Management Studio (SSMS). Furthermore, we can create information using either the graphical interface tools or with Transact-SQL (T-SQL) commands.

SQL Server Management Studio Data Entry Tools

The simplest way to manually enter small amounts of information is to use the SSMS data entry tools. To try this, open the "JoBS" database branch of the Object Explorer tree in SSMS. Expand the "Tables" node to see the list of tables that have been created. You can now select the table for which you wish to add data rows.

Right-click the "Skills" table and choose "Open Table" from the menu. A grid appears with one column for each column in the table. You can now simply add and edit rows in the table by typing.

Add the rows shown below to the table to set up the basic skills that may be assigned to engineers and jobs. As you tab across the table cells, notice that entering invalid information, such as a SkillCode with more that three characters, is not permitted. As you leave each row, the data is committed to the database. If you make an error, you can simply modify the information to update a data row or highlight the entire row and press the delete key to remove it completely.

Once you have added all four rows, your grid should look similar to the image below. Click the close button at the top-right of the table's window to close the grid. You can also select "Close" from the File menu.

NB: The final row showing two NULL values appears in readiness for a fifth data row to be added. This empty row is not stored in the table.

The INSERT Statement

When developing software, the above graphical user interface-based method of creating information is useful for test data. However, in the software itself you are far more likely to want to generate T-SQL commands that create information in response to user actions. To do so, you can use the INSERT statement.

The simplest form of the INSERT statement is used when adding a full row of data to a table. The syntax for the command is as follows:

The table-name specifies the table that you wish to add a row to. The data to be created is then provided in a comma-separated list. The list of values must be in the same order as the columns within the table so that information is not added in the wrong columns.

We can use this syntax to add a row to the Parts table in the database. This table has three columns to hold an alphanumeric part number, a description of the part and the unit cost. Using a new query window in SSMS, run the following command. Note the use of apostrophes around the data for the first two columns. These delimiters are required for character-based information.

If the command executes correctly, you will see a response that indicates that one row was affected.

You can see the new row in the table by opening the table in the data entry mode described earlier. Alternatively, you can list the entire contents of the table using a SELECT statement. Try executing the following to see the data in the query window. NB: The SELECT statement is described in detail in the next article in this series.

Using Named Columns

The simple form of inserting data using the INSERT statement can be problematic. As the data being stored is not explicitly applied to a column, a change to the structure of the table could leave existing statements invalid. If, for example, a column is added or the column order is changed, statements embedded in software would become incorrect and have to be changed. To avoid this, the column names should also be included in the command.

INSERT INTO table-name
    (column1, column2, ..., columnN)
VALUES
    (value1, value2, ..., valueN)

This syntax is more resilient to change than the previous version. An additional improvement is that not every column must be specified in the statement. If a column name is excluded, a default value or a null value is stored when creating the new row. If the column does not permit null values and has no default value an error will occur.

Let's add another row to the Parts table using the improved syntax:

INSERT INTO Parts
    (PartNumber, PartName, Cost)
VALUES
    ('22COPIPE', '22mm Copper Pipe', 2.97)

Inserting Different Data Types

As we have seen, when inserting character data the information must be provided within apostrophes whereas numeric information does not require delimiters. Each data type has different requirements, described below:

Date and Time Data

Date information can be provided in many formats, including versions with named months, such as '27 July 2008' or numeric formats such as '27/07/2008'. Numeric formats should be used with care, particularly where the application may be used in more than one country. Depending upon the local settings for the SQL Server, the format may be incorrectly interpreted. For example, '06/07/08' could be 6 July 2008, 7 June 2008 or 8 July 2006, depending upon the local interpretation. A safer option is the ISO 8601 format. This specifies that dates be provided as YYYY-MM-DD, eg. '2008-07-27' for 27 July 2008.

To comply with the ISO standard, the time portion of date and time information should be supplied in 24-hour format with the hours, minutes and seconds separated by colons, eg. '16:20:25'. If fractions of seconds are required, these can be included. You may also decide to omit the seconds or the entire time portion if these are not required.

To demonstrate the creation of date and time information try the following statements, which will create complaints. Note the use of apostrophes to delimit the dates.

INSERT INTO Complaints
    (ComplaintReference, CustomerNumber, ComplaintTime, Complaint)
VALUES
    (1, 23, '2008-07-23', 'Engineer did not arrive.')
    
INSERT INTO Complaints
    (ComplaintReference, CustomerNumber, ComplaintTime, Complaint)
VALUES
    (2, 19, '2008-07-26 14:02:25', 'Leak in radiator still present.')

UniqueIdentifer Data

Globally unique identifiers (GUIDs) are thirty-two digit hexadecimal numbers with digits organised into five groups of eight, four, four, four and twelve digits respectively. To insert a specific GUID into a uniqueidentifier column, these groups must be separated using hyphens (-) and the entire value must be delimited using apostrophes. For example:

INSERT INTO UsedParts
    (JobId, PartNumber, UnitsUsed, Cost)
VALUES
    ('12345678-1234-1234-1234-123456789ABC', '22COPIPE', 3, 2.97)

You can also generate GUIDs automatically when you wish to create new unique values.

Binary Data

It is usual to insert binary data into a database using a graphical user interface tool or application that generates the binary information and appropriate scripts in the background. However, it is possible to provide binary data directly in T-SQL scripts in SSMS. To do so, the binary is represented as a continuous set of two-digit hexadecimal bytes. To indicate that this format is being used, the string of digits is prefixed with "0x", as with the photograph column in the following example:

INSERT INTO Engineers
    (EngineerId, EngineerName, HourlyRate, Photograph)
VALUES
    (95, 'James Fields', 28.50, 0x1234567890ABCDEF)

Inserting Timestamp Data

If a table has a timestamp column it will be set automatically when a row is inserted in the table. It will also be updated automatically whenever the row is modified. The data is this type of column is completely controlled by SQL Server and may not be explicitly specified in an INSERT statement. If the column is included in the command, the value applied must be null and will be replaced with a new timestamp value.

Inserting Null Data

When you wish to indicate that the information in a column is undefined, the column value should be set to null. This is achieved using the NULL keyword, as follows. NB: In this case the column would be NULL even if it were excluded from the INSERT statement as it has no default value.

INSERT INTO Engineers
    (EngineerId, EngineerName, HourlyRate, Photograph)
VALUES
    (96, 'Rachel Moran', 28.50, NULL)

Inserting Apostrophes in Character Data

The final item to be considered in this section returns to character types. As the data to be inserted is delimited using apostrophes, an apostrophe cannot simply be included in the text being stored. Instead, the apostrophe to be added to the row should be included twice in the statement. SQL Server automatically converts the two apostrophes into a single character in the row. Try executing the following and then reviewing the table's data to demonstrate this.

INSERT INTO Engineers
    (EngineerId, EngineerName, HourlyRate, Photograph)
VALUES
    (97, 'Jack O''Leary', 28.25, NULL)

Updating Data

Once you have information in your database you can modify it using the UPDATE statement. This command requires that you specify the table to be modified followed by a comma-separated list of the changes that you wish to make to individual columns. Columns that are to remain unchanged are not included in the list. The basic syntax of the statement is as follows:

UPDATE table-name
SET column1 = value1,
    column2 = value2,
    ...,
    columnN = valueN

Using this syntax, every row in the specified table is updated. For example, to increase the hourly rate of every engineer by one pound, execute the following command. Note the use of the column name in a calculation to the right of the assignment operator (=). The new value for the column can be a literal value or can be calculated.

UPDATE Engineers
SET HourlyRate = HourlyRate + 1

The WHERE Clause

Although the above syntax for updating information is useful, more often you will want to update a single item or a set of rows that meet a given criteria. This is achieved by adding a WHERE clause to the statement. The WHERE clause contains a condition that is checked for every row in the table. If the condition is met, the row is updated. If not, the row remains unchanged.

To demonstrate the WHERE clause, execute the update statement below. This statement increases the hourly rate for all engineers that currently have a rate of £29.50 or greater. If the engineer is paid less than £29.50, their rate remains the same.

UPDATE Engineers
SET HourlyRate = HourlyRate + 0.25
WHERE HourlyRate >= 29.50

The WHERE clause will be considered in greater detail in the next article in this series.

Deleting Data

The last T-SQL data manipulation command to consider is DELETE. This command simply removes rows from a table. As with UPDATE, a DELETE statement can be executed without a WHERE clause to empty a table, or with a WHERE clause to delete rows that match specified criteria.

To demonstrate, we will delete all engineers with an hourly rate over £29.50:

NB: Note the use of the "FROM" keyword. This is optional but some SQL developers prefer to include it.