How To Load Data Into Azure SQL Database [Azure SQL Query Editor]

by
How To Load Data Into Azure SQL Database

You have already learned how to Create SQL Database in Azure and how you connect it through SSMS. Now it’s time to load data into Azure SQL database in order to query and see the results.

We are going to use sample test data available on Microsoft website to demonstrate how it works. Before you start make sure you have already followed our previous tutorials listed below to start with this article.

To begin with data loading process first we need to have row data for demonstration & practice purpose.

Download Sample Data

You can download sample data from here –

Create Sample Empty Tables in your Azure SQL Database

In this tutorial you are going to create four empty tables to build Student Management system for university using SQL queries.
  • Person
  • Course
  • Student
  • Credit
  1. Open SSMS and navigate to Object Explorer, right click on your database and select New Query.
  2. Execute following query from query window to create sample empty tables in your database.
-- Create Person table
CREATE TABLE Person
(
    PersonId INT IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(128) NOT NULL,
    MiddelInitial NVARCHAR(10),
    LastName NVARCHAR(128) NOT NULL,
    DateOfBirth DATE NOT NULL
)
-- Create Student table
CREATE TABLE Student
(
    StudentId INT IDENTITY PRIMARY KEY,
    PersonId INT REFERENCES Person (PersonId),
    Email NVARCHAR(256)
)
-- Create Course table
CREATE TABLE Course
(
    CourseId INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Teacher NVARCHAR(256) NOT NULL
)
-- Create Credit table
CREATE TABLE Credit
(
    StudentId INT REFERENCES Student (StudentId),
    CourseId INT REFERENCES Course (CourseId),
    Grade DECIMAL(5,2) CHECK (Grade <= 100.00),
    Attempt TINYINT,
    CONSTRAINT [UQ_studentgrades] UNIQUE CLUSTERED
    (
        StudentId, CourseId, Grade, Attempt
    )
)
Create tables in Microsoft Azure SQL database

Now you have created four empty tables under MyAzureSQLServer-Database. Let’s try to fill the sample data into those tables.

Upload Sample data into Azure SQL Server Database

1. Open Command prompt and navigate to Download folder where you have downloaded above listed data.

In our case, we have downloaded all the data into C:\Users\Mayur\Downloads directory.

 Microsoft Windows [Version 10.0.00000.000]
 (c) 2019 Microsoft Corporation. All rights reserved.
  
 H:\>C:
  
 C:\>cd C:\Users\Mayur\Downloads
  
 C:\Users\Mayur\Downloads>

2. Execute the following commands in CMD (command prompt) to insert sample data into the tables replacing the values for server, database, user, and password with the values for your environment.

bcp Course in SampleCourseData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t ","
bcp Person in SamplePersonData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t ","
bcp Student in SampleStudentData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t ","
bcp Credit in SampleCreditData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t ","

We have used following commands –

C:\Users\Mayur\Downloads>bcp Course in SampleCourseData -S myazuresql-server.database.windows.net -d myazuresqlserver-database -U sqladmin -P xxxxx -q -c -t ","
Starting copy...
30 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 110    Average : (272.73 rows per sec.)
C:\Users\Mayur\Downloads>
C:\Users\Mayur\Downloads>bcp Person in SamplePersonData -S myazuresql-server.database.windows.net -d myazuresqlserver-database -U sqladmin -P xxxxx -q -c -t ","
Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 172    Average : (5813.95 rows per sec.)
C:\Users\Mayur\Downloads>
C:\Users\Mayur\Downloads>bcp Person in SamplePersonData -S myazuresql-server.database.windows.net -d myazuresqlserver-database -U sqladmin -P xxxxx -q -c -t ","
Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 172    Average : (5813.95 rows per sec.)
C:\Users\Mayur\Downloads>
C:\Users\Mayur\Downloads>bcp Student in SampleStudentData -S myazuresql-server.database.windows.net -d myazuresqlserver-database -U sqladmin -P xxxxx -q -c -t ","
Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 203    Average : (4926.11 rows per sec.)
C:\Users\Mayur\Downloads>
C:\Users\Mayur\Downloads>bcp Credit in SampleCreditData -S myazuresql-server.database.windows.net -d myazuresqlserver-database -U sqladmin -P xxxxx -q -c -t ","
Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 156    Average : (6410.26 rows per sec.)
C:\Users\Mayur\Downloads>

Query on Azure SQL Database tables through SSMS

Execute following commands through SSMS query.

Select count(*) as Course from dbo.Course ;
Select count(*) as Credit from dbo.Credit ;
Select count(*) as Person from dbo.Person ;
Select count(*) as Student from dbo.Student ;
SQL Queries on Azure SQL database from SSMS

Query on Azure SQL Database tables through Azure Portal (Query Editor)

  1. Logon to Azure portal and Navigate to Home>SQL Database and Click on your Database Name. Open Query Editor from left hand side panel.
  2. Login with user id and password.
Azure SQL Query Editor
  1. Execute following sql query from Query panel.
SELECT TOP (1000) * FROM [dbo].[Course]
Microsoft Azure SQL Query Editor

Disclaimer: *Sample Data links mentioned in this article available for learning purpose on Microsoft website. It doesn’t belong to us or we do not own it.

Leave a Comment