Running a query

To run a query, click the database in the Object Explorer on the left, and then click the New Query button near the top of the window. That opens a tab where you can enter SQL code. A tutorial introducing SQL can be found at https://www.w3schools.com/sql/sql_intro.asp.

The version of SQL used by Microsoft SQL Server is called T-SQL. Microsoft’s reference guide for T-SQL is at https://learn.microsoft.com/en-us/sql/t-sql/language-reference.

Because you will be working with multiple databases and schemas, it is important to understand how to use “fully qualified” names for tables and views, which have this format:

[DatabaseName].[SchemaName].[TableName]

You do not need to include the database name if you are querying an object within the database to which you are connected. You also do not need the brackets if a name contains no spaces or illegal characters.

So, for example, when you are within the FellowsSample database, to select the first 100 patients in your project, you can leave out the database name and brackets, but you will need the schema:

select top 100 * from S00.patient_dimension;

However, if you are in your project database (S00), you also need to indicate the database (FellowsSample) since you are referencing a table in a different database.

select top 100 * from FellowsSample.S00.patient_dimension;

Similarly, if you are in the FellowsSample database, and you want to save results to your project database, you need to include the project database name and a schema. The default schema in the project databases is “dbo”. For example:

select top 100 * into S00.dbo.my_patients from S00.patient_dimension;

In this example, note that the table “my_patients” is being written to a database named S00, while the data is being read from a table “patient_dimension” in a schema named S00.

Our suggestion is always to always work from within your project database and use fully qualified names when referencing the PatientSample views. Being consistent with that will help you avoid mistakes.