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.
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:
INSERT INTO table-name VALUES (value1, value2, ..., valueN)
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.
INSERT INTO Parts VALUES ('15COPIPE', '15mm Copper Pipe', 1.52)
If the command executes correctly, you will see a response that indicates that one row was affected.
(1 row(s) 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)
(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)
('22COPIPE', '22mm Copper Pipe', 2.97)
27 July 2008