How to: Create, Read, Update, and Delete Data using Transact-SQL

Article
09/22/2010

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

This topic provides examples of using Transact-SQL to create, read, update, and delete data in the SQL Server Modeling Services database.

Note

This topic uses a HumanResources model. For more information about how to create this model and install it into the Modeling Services database, see How to: Install the HumanResources Sample Model using Visual Studio.

At the end of this tutorial, you will use Transact-SQL statements to do the following tasks.

Obtain the identifier of existing Folders by using the [Repository.Item].[PathsFolder] function.
Create a new Folder in the [Repository.Item].[Folders] view.
Add employee instances to the [HumanResources].[Employees] table.
Query the [HumanResources].[Employees] table to display the new data.
Update the employee information.
Remove the Folder and its contents.

To setup a new query window in SQL Server Management Studio

In SQL Server Management Studio, in the Connect to Server dialog, type the Server name of the instance of SQL Server that has the Modeling Services database.
Specify the authentication properties, and then click the Connect button.
On the toolbar, click the New Query button. A blank query window should open.
In the blank query window, add the following Transact-SQL statements. These statements change the context to the Repository database, which is the default Modeling Services database installed by the SQL Server Modeling CTP. The following statements also create variables for Folders and the new employee identifier.
```
use Repository
go

declare @parentFolderId as int
declare @folderId as int
declare @employeeId as bigint
```

To obtain a Folder identifier for a given path

In the same query window, add statements that create a target Folder, HumanResourcesFolder. These statements call the [Repository.Item].[PathsFolder] function to look for an existing Modeling Services Folder based on the path name, Repository/HumanResourcesFolder. If this Folder does not exist, it inserts a new Folder into the [Repository.Item].[Folders] view. The new Folder is a subfolder of the top-level Repository folder.
```
-- Obtain the top-level Repository Folder.
set @parentFolderId = [Repository.Item].[PathsFolder]('Repository')

-- Look for the existence of the target Folder.
set @folderId = [Repository.Item].[PathsFolder]('Repository/HumanResourcesFolder')

-- If the target Folder does not exist, create it.
if (@folderId is null)
begin
    -- Insert the target Folder, HumanResourcesFolder.
    insert into [Repository.Item].[Folders] ([Name], [Folder])
    values(N'HumanResourcesFolder', @parentFolderId)

    -- Obtain the identifier for the newly insert Folder.
    set @folderId = [Repository.Item].[PathsFolder]('Repository/HumanResourcesFolder')
end
```
Note that you must call [Repository.Item].[PathsFolder] to retrieve the identity of the newly inserted Folder. In Transact-SQL, two other methods of retrieving the most recent identifier after an insertion are the scope_identity and the @@identity functions; however, these will not work properly in this case. The insertion into the [Repository.Item].[Folders] view results in a trigger call that does the insertion into the underlying table, [Repository.Item].[FoldersTable]. This means that the insertion is done at a different scope than the current call, and scope_identity returns a null value. Using @@identity can produce the correct result, but there is a chance that you will get an incorrect identifier. The insertion into the [Repository.Item].[Folders] view also inserts rows into related security tables, and the @@identity function returns the last inserted identity from the call. This means that the returned identity might be the identity from another table rather than the desired identity of the newly inserted Folder.

To add a new employee to the [HumanResources].[Employees] table

In the same query window, add statements that insert a new row in the [HumanResources].[Employees] table.
```
-- Insert a new employee into the [HumanResources].[Employees] view.
insert into [HumanResources].[Employees] (Folder, Name)
   values(@folderId, 'John')

-- Obtain the identifier of the inserted row.
set @employeeId = @@identity
```
Note

This new employee instance belongs to the Folder that was passed into the Folder parameter. Each table and view that follows the Folder pattern has a Folder column that associates rows with their corresponding Folders.

To verify the newly added row, add a simple select statement on the [HumanResources].[Employees] table.

-- Display the results.
select Id, Folder, Name
from [HumanResources].[Employees] 
where Id = @employeeId

To update existing data in the [HumanResources].[Employees] table

Update the name of the employee from John to Jonathan. After the update, display the results again.

-- Update the name 'John' to 'Jonathan'.
update [HumanResources].[Employees]
set Name = 'Jonathan'
where Id = @employeeId

-- Display the results.
select Id, Folder, Name
from [HumanResources].[Employees] 
where Id = @employeeId

To remove a Folder and all of its associated data

Delete the HumanResourcesFolder Folder in the [Repository.Item].[Folders] view. The Modeling Services Folder pattern specifies that the contents of the Folder are also removed. As a result, this deletes the previously added row in the [HumanResources].[Employees] table.

-- Remove the Folder, which deletes the Folder contents.
delete [Repository.Item].[Folders] 
where Id = @folderId

-- Run the same query, but it should not return any rows following the Folder removal.
select Id, Folder, Name
from [HumanResources].[Employees] 
where Id = @employeeId

To test the Transact-SQL script

Press the F5 key to run the Transact-SQL script.
Observe the three query results in the results pane. The first result shows the new employee row. The Folder column contains the identifier of the new Folder in this example. The second result shows the updated row with the changed Name field. The final result displays no rows, proving that the removal of the containing Folder removed the employee row as well.

Example

The following is the complete script for this example.

use Repository
go

declare @parentFolderId as int
declare @folderId as int
declare @employeeId as bigint

-- Obtain the top-level Repository Folder.
set @parentFolderId = [Repository.Item].[PathsFolder]('Repository')

-- Look for the existence of the target Folder.
set @folderId = [Repository.Item].[PathsFolder]('Repository/HumanResourcesFolder')

-- If the target Folder does not exist, create it.
if (@folderId is null)
begin
    -- Insert the target Folder, HumanResourcesFolder.
    insert into [Repository.Item].[Folders] ([Name], [Folder])
    values(N'HumanResourcesFolder', @parentFolderId)

    -- Obtain the identifier for the newly insert Folder.
    set @folderId = [Repository.Item].[PathsFolder]('Repository/HumanResourcesFolder')
end

-- Insert a new employee into the [HumanResources].[Employees] view.
insert into [HumanResources].[Employees] (Folder, Name)
   values(@folderId, 'John')

-- Obtain the identifier of the inserted row.
set @employeeId = @@identity

-- Display the results.
select Id, Folder, Name
from [HumanResources].[Employees] 
where Id = @employeeId

-- Update the name 'John' to 'Jonathan'.
update [HumanResources].[Employees]
set Name = 'Jonathan'
where Id = @employeeId

-- Display the results.
select Id, Folder, Name
from [HumanResources].[Employees] 
where Id = @employeeId

-- Remove the Folder, which deletes the Folder contents.
delete [Repository.Item].[Folders] 
where Id = @folderId

-- Run the same query, but it should not return any rows following the Folder removal.
select Id, Folder, Name
from [HumanResources].[Employees] 
where Id = @employeeId