CRUD Statements  «Prev 

Why are server-side cursors important?

For over a decade now SQL Server and other DBMS developers have been using server-side cursors to access their databases and scroll through updatable rowsets. This connected approach assumes that the database connection remains in place while the application runs a query and builds a server-side set of rows that can be retrieved and updated as needed. For a litany of reasons, Microsoft chose not to implement any server-side cursor functionality in their new .NET Framework data access interface ADO.NET. This page discusses how you can work around this limitation to create and manage your own server-side cursors. Server-side cursors are especially useful when working with highly interactive applications especially when the application cannot work with disconnected (static) data. This type of application needs a mechanism to work with a single row or a small set of rows at once. Server-side cursors are designed to meet this need. As illustrated by the examples in this article, I will show you how to:
  1. Create a cursor based on a focused SELECT statement.
  2. Position a cursor to any designated row.
  3. Change the data in the currently selected cursor row.
  4. Adapt the cursor so other users changes are visible (or not).

Insert Statement using Subqueries

Break this code down into pieces. There are two main sections of code.

The first is an INSERT statement

The insert statement gets its values by using a SELECT statement. This is NOT a subquery, but part of most INSERT statements.

The second section of code is the actual subquery which is within the NOT EXISTS clause. This clause is used to test for the existence of something.

In this case, one of the conditions of the INSERT statement is to test for the existence of the FirstName of Micky, LastName of Mouse, and PriorSalary of 20000.

Therefore, data will only be inserted if there is not already a record containing this data in the SalaryHistory table.