How to Import and Export SQL Server Data to an Excel File?

SQL Server is very popular in Relational Database and it is used across many software industries. Portability of data is a much-required feature of any database. i.e. Database should support features like exporting database data to Excel/CSV/JSON and also should import data from them. In this article, let us see how to import and export SQL Server Data to an Excel file.  Azure data studio is a very useful tool for export options and the best part is it can run on Windows/Linux/Mac Operating system. 

Method 1: exporting data to Excel via Azure DataStudio

Step 1: Database creation 

We should have a  database to proceed further. Let us keep ‘GEEKSFORGEEKS’ as the database name.

Syntax:

--Command to create database:
 - CREATE DATABASE <dbname>;
Create Database GEEKSFORGEEKS:

Query:

--Make the database active
USE GEEKSFORGEEKS;

Step 2: Adding the tables to the database 

Creating a table with a Primary Key. Each author will have their unique ID and hence it is a Primary Key.

Query:

CREATE TABLE Posts (
    ID INT IDENTITY NOT NULL PRIMARY KEY,
    PostName VARCHAR(255) NOT NULL,
    Category VARCHAR(255),
    Likes INT,
    Shares INT
);
GO

Step 3: Inserting rows in the table. We can add only the necessary columns.

Query:

insert into Posts(PostName,Category,Likes,Shares)
 VALUES ('Core Java Programming','Java',10,5);
insert into Posts(PostName,Category,Likes,Shares) 
VALUES ('SQLServer Programming','Java',20,8);
insert into Posts(PostName,Category,Likes,Shares)
 VALUES ('Oracle Programming','Java',25,12);
insert into Posts(PostName,Category,Likes,Shares)
 VALUES ('MongoDB Programming','Java',50,18);
insert into Posts(PostName,Category,Likes,Shares) 
VALUES ('Python Programming','Java',15,10);

Step 4: Let us do a general query

Query:

SELECT * FROM Posts;

Output:

Step 5: Suppose if we want to exporting data to Excel via Azure Datastudio

Azure Datastudio has come with rich features for exporting data to Excel. Even exporting to CSV/JSON/XML/Chart are also possible. As given in below screenshot, need to click the option in the right corner

Output:

On clicking on “Save As Excel”, the resultant output is saved in excel format. We can see the saved contents below:

Step 6: We can write queries as per our needs and those data alone also can be exported. i.e. ultimately whatever results are in the “Results” pane are saved into excel format.

Query:

-- To get the details of Posts in 
which category is like Database

SELECT PostName,Category,Likes,Shares FROM Posts
WHERE Category LIKE '%Database%'
ORDER BY PostName

Output:

This output can be exported to excel by clicking on “Save as Excel”, upon completion we can see the results as

Method 2: Import data from EXCEL into SQL server using SQL Server Management Studio

Importing data via SQL Server Management Studio. This is an easier option.

Step 1:  Click on “Tasks” and choose “Import Data” option.

Step 2: The below screens are self-explanatory. Let us see one by one.

Step 3:Click on “Microsoft Excel” option. 

Step 4: Source location can be selected as given below

Step 5: Destination location can be chosen from this window

Step 6: We can see that a table with the name “dataFromExcelForPostsByCategory” is created. If we compare the contents of excel data, they are the same.

In every database, portability is much important. In SQL server, by above-mentioned ways, they are done. For export, using Azure data studio it is explained and for import, by using  SQL Server Management Studio, it is explained.