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

by

You’ve already learned how to create a SQL Database in Azure and how to connect to it using SQL Server Management Studio (SSMS). Now, it’s time to load data into Azure SQL Database so you can run queries and analyze the results.

For this demonstration, we’ll use sample test data provided by Microsoft. Before you begin, ensure you’ve completed the previous tutorials listed below:

To start the data loading process, we’ll first need some sample data for practice and demonstration purposes.

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
    )
)
Load Data Into 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

STEP 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>

STEP 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 to use 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 ;
How to Load Data Into Azure SQL Database

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.
Microsoft Azure SQL Database Query Editor
  1. Execute following sql query from Query panel.
				SELECT TOP (1000) * FROM [dbo].[Course]
How to Load Data Into Azure SQL Database tables

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.

ADDITIONAL RESOURCES: