How to: Use the T-SQL Console to Explore and Edit Data

[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 shows how to execute Transact-SQL commands in the Microsoft code name “Quadrant” T-SQL Console to examine and edit data. By using the T-SQL Console, you can perform bulk editing on multiple records in a database without repetitive UI operations.

This topic uses sample data that is installed in How to: Install the "Quadrant" Sample Data.

To examine data by using Transact-SQL commands

1. On the File menu, click New, and then click Session. Select the Repository database to connect.

2. On the File menu, click New, and then click T-SQL Console. The console automatically connects to the database that is associated with the active workpad session in the workspace.

3. The console opens in a new workpad. To see a list of available commands, type #help at the prompt and press ENTER.

4. Type #info at the prompt and press ENTER to confirm the current connection is to the Repository database.

   The following result should be returned.

   Connected to: .

   Database: Repository

   State: Open

5. Type the following query at the prompt and press ENTER.

   SELECT TOP 3 [Subject] FROM [Repository].[Microsoft.Samples.Organization].[Tasks]
   

6. At the next prompt, type GO and press ENTER to execute the Transact-SQL command, which will return the Subject of the first 3 records in the Tasks table. The following should be returned:

   Subject

   ---------------------

   Interview Candidates

   Interview HR

   Benchmarks   

7. At the prompt, copy and paste the following code.

   SELECT TOP 3 [Id], [Subject] FROM [Repository].[Microsoft.Samples.Organization].[Tasks]
   

8. At the next prompt, type GO and press ENTER to execute the Transact-SQL command, which will return the Id and Subject of the first 3 records in the Tasks table. The following should be returned:

   Id  Subject

   --- --------------------- 

   1   Interview Candidates  

   2   Interview HR          

   3   Benchmarks

9. To clear the console, type #clear at the prompt and press ENTER.

10. To close the current connection, type #disconnect at the prompt and press ENTER. The following should be returned:

    Connected to: .

    Database:

    State: Closed

To connect to a specific database

1. Since we have disconnected from the server in the previous procedure, we need to reconnect. Type the following command at the prompt and press ENTER.

   #connect .
   

2. The following should be returned:

   Connected to: .

   Database: master

   State: Open

3. The previous step only connects to the default database instance in the server. To explicitly connect to the Repository database, type the following command at the prompt.

   use Repository
   

4. At the next prompt, type GO and press ENTER to execute the Transact-SQL command. The following should be returned:

   Command(s) completed successfully.

5. Type #info at the prompt and press ENTER to confirm the current connection is to the Repository database.

To create a new record by using T-SQL commands

1. Type the following command at the prompt and press ENTER.

   insert into [Repository].[Microsoft.Samples.Organization].[Tasks] (Subject, Folder) values ('New Task from T-SQL', 106)
   

2. At the next prompt, type GO and press ENTER to execute the Transact-SQL command. The command will create a new task named “New Task” in the Tasks folder, which has an Id of 106.

3. The following response indicates that the command completed successfully and a row has been inserted to the Tasks table.

   1 rows affected.

4. Return to the Repository Explorer that we opened in the previous procedure. Expand the Database node.

5. Expand the Microsoft.Samples.Organization node. Double-click the Tasks node to open it in a new workpad. The new Tasks workpad displays all the task records in a table view.

6. Note the appearance of New Task from T-SQL in the table.

To modify an existing record by using Transact-SQL commands

1. In this procedure, we are going to modify the New Task from T-SQL task that we created previously.

   In the Tasks workpad, note the value of the Id field of the new task.

2. Type the following command at the prompt, replace n with the actual Id value, and press ENTER.

   UPDATE [Repository].[Microsoft.Samples.Organization].[Tasks] SET Priority = 'High' WHERE Id = n
   

3. At the next prompt, type GO and press ENTER to execute the Transact-SQL command, which will modify the Priority field of the new task to High.

4. The console returns the following, indicating that the command completed successfully, and a row has been modified to the Tasks table:

   1 rows affected.

5. Return to the Tasks workpad and press F5 to refresh the data. Note that the Priority field of the new task is now set to High.

See Also

Other Resources

"Quadrant" Tutorials