Thursday 7 June 2018

Azure SQL Database: Use SQL Server Management Studio to connect and query data

Azure SQL Database: Use SQL Server Management Studio to connect and query data

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database for Microsoft Windows. This quickstart demonstrates how to use SSMS to connect to an Azure SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

Prerequisites

This quickstart uses as its starting point the resources created in one of these quickstarts:

Install the latest SSMS

Before you start, make sure you have installed the newest version of SSMS.

SQL server connection information

Get the connection information needed to connect to the Azure SQL database. You will need the fully qualified server name, database name, and login information in the next procedures.
  1. Log in to the Azure portal.
  2. Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.
  3. On the Overview page for your database, review the fully qualified server name as shown in the following image. You can hover over the server name to bring up the Click to copy option.
    server-name
  4. If you forget your server login information, navigate to the SQL Database server page to view the server admin name. If necessary, reset the password.

Connect to your database

Use SQL Server Management Studio to establish a connection to your Azure SQL Database server.
Important
An Azure SQL Database logical server listens on port 1433. If you are attempting to connect to an Azure SQL Database logical server from within a corporate firewall, this port must be open in the corporate firewall for you to successfully connect.
  1. Open SQL Server Management Studio.
  2. In the Connect to Server dialog box, enter the following information:
    Setting    Suggested valueDescription 
    Server typeDatabase engineThis value is required.
    Server nameThe fully qualified server nameThe name should be something like this: mynewserver20170313.database.windows.net.
    AuthenticationSQL Server AuthenticationSQL Authentication is the only authentication type that we have configured in this tutorial.
    LoginThe server admin accountThis is the account that you specified when you created the server.
    PasswordThe password for your server admin accountThis is the password that you specified when you created the server.
    connect to server
  3. Click Options in the Connect to server dialog box. In the Connect to database section, enter mySampleDatabase to connect to this database.
    connect to db on server
  4. Click Connect. The Object Explorer window opens in SSMS.
    connected to server
  5. In Object Explorer, expand Databases and then expand mySampleDatabase to view the objects in the sample database.

Query data

Use the following code to query for the top 20 products by category using the SELECT Transact-SQL statement.
  1. In Object Explorer, right-click mySampleDatabase and click New Query. A blank query window opens that is connected to your database.
  2. In the query window, enter the following query:
    SQL
    SELECT pc.Name as CategoryName, p.name as ProductName
    FROM [SalesLT].[ProductCategory] pc
    JOIN [SalesLT].[Product] p
    ON pc.productcategoryid = p.productcategoryid;
    
  3. On the toolbar, click Execute to retrieve data from the Product and ProductCategory tables.
    query

Insert data

Use the following code to insert a new product into the SalesLT.Product table using the INSERT Transact-SQL statement.
  1. In the query window, replace the previous query with the following query:
    SQL
    INSERT INTO [SalesLT].[Product]
            ( [Name]
            , [ProductNumber]
            , [Color]
            , [ProductCategoryID]
            , [StandardCost]
            , [ListPrice]
            , [SellStartDate]
            )
      VALUES
            ('myNewProduct'
            ,123456789
            ,'NewColor'
            ,1
            ,100
            ,100
            ,GETDATE() );
    
  2. On the toolbar, click Execute to insert a new row in the Product table.
    insert

Update data

Use the following code to update the new product that you previously added using the UPDATE Transact-SQL statement.
  1. In the query window, replace the previous query with the following query:
    SQL
    UPDATE [SalesLT].[Product]
    SET [ListPrice] = 125
    WHERE Name = 'myNewProduct';
    
  2. On the toolbar, click Execute to update the specified row in the Product table.
    update

Delete data

Use the following code to delete the new product that you previously added using the DELETE Transact-SQL statement.
  1. In the query window, replace the previous query with the following query:
    SQL
    DELETE FROM [SalesLT].[Product]
    WHERE Name = 'myNewProduct';
    
  2. On the toolbar, click Execute to delete the specified row in the Product table.
    delete
Actual Post: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-ssms

No comments:

Post a Comment

Adding a newline into a string in C# and X++

Below is the sample code we can use for  adding a newline after every occurrence of "@" symbol in the string in C#   using System...