Skip to content

Martini Data Integration SQL Database Views

Overview

SQL views are virtual tables representing data from one or more tables. Views can simplify complex queries, enhance security, and isolate database changes.

Creating a View

To create a view in MSSQL, use the CREATE VIEW statement. Here’s an example of creating a view that selects the PetName from a Pets table where the PetId is 6:

1
2
CREATE VIEW view_pets AS
SELECT PetName FROM Pets WHERE PetId = 6;

This statement creates a new view named view_pets that will display the names of pets with a PetId of 6.

Managing Views

Once a view is created, it can be managed through SQL commands:

  • Updating a View: Use ALTER VIEW to modify a view without dropping it.
  • Removing a View: Use DROP VIEW to remove a view when it's no longer needed.

Remember that changes to views can impact any database processes or applications relying on them. Proper caution and communication are advised before making alterations.

Utilizing Views

After creating a view, you can easily access and interact with the data it represents. For instance, if you want to view the details of a pet with the PetId of 6, which you've set up in the view_pets, you can execute a SELECT statement against that view.

Here's how you can retrieve the information from the view_pets view:

1
SELECT * FROM view_pets;

When executed, this statement will return the PetName of the pet with PetId of 6, as the view was designed to filter the data accordingly.

Example Output

Running the above query against the view_pets view might produce the following output:

1
2
3
PetName
-------
Fluffy

This indicates that there is one pet with the PetId of 6 and its name is Fluffy.

Benefits of Using Views

  • Simplification of Queries: Views can turn complex queries into simple SELECT statements.
  • Security: Views can provide a secure way to give access to the data without exposing the underlying table structures.
  • Consistency: Views can present a consistent, unchanging picture of the structure of the data, even if the underlying source tables are changed.

Executing Views in Martini

Once you have established a connection to your database server in Martini and created a SQL view, you can execute this view directly within Martini's SQL Service.

Connecting to the Database Server

  1. In Martini, navigate to the database connections settings.
  2. Select the database where your view is located, for example, store-pets.

Creating a New SQL Service

  1. Go to the SQL Service creation interface in Martini.
  2. Ensure that the SQL Service is using the correct database connection.
  3. You'll be presented with an editor where you can type your SQL commands.

Querying the View

In the SQL Service editor, enter the command to query the view you created:

1
SELECT * FROM view_pets;

Executing the Command

Execute the command within the editor. The output will display the results of the query against the view_pets view. For instance, if you had previously created a view to display the name of a pet with PetId of 6, executing this query will return that pet's name.

Visual Interface

The interface should display two main panels: the source structure on the left and the editor on the right, resembling the following layout:

1
2
3
4
5
6
7
-----------------------------------------
|           |                           |
|  Source   |   Editor                  |
|           |                           |
|           | 1 SELECT * FROM view_pets |
|           |                           |
-----------------------------------------

Make sure to save your SQL Service after creation to use it in your integration processes.