Export SQL Server Data From Table to CSV File

SQL Server is a very popular relational database because of its versatility in exporting data in Excel, CSV, and JSON formats. This feature helps with the portability of data across multiple databases.

Here, we will learn how to export SQL Server Data from a table to a CSV file. Tools like Azure Data Studio are very useful for exporting tables in SQL Server, and the best part is that it can run on Windows/Linux/Mac Operating systems.

To export table data to a CSV file in SQL Server you can use Azure DataStudio or SQL Server Management Studio. Let’s discuss each of these methods below.

Exporting data to CSV File using Azure DataStudio

Here is a step-by-step process for exporting data to a CSV file using Azure DataStudio.

Step 1: We should have a database to proceed further. Let us keep ‘w3wiki’ as the database name.

Query:

-- Check whether 'w3wiki' exists and if it is there drop it
DROP DATABASE IF EXISTS w3wiki;
--Command to create database:
- CREATE DATABASE <dbname>;
Create Database w3wiki:

Query:

--Make the database active
USE w3wiki;

Step 2: Adding tables to the database

Query:

CREATE TABLE [Address](
  [AddressID] [int] IDENTITY(1,1)  NOT NULL,
  [AddressLine1] [nvarchar](60) NOT NULL,
  [AddressLine2] [nvarchar](60) NULL,
  [City] [nvarchar](30) NOT NULL,
  [StateProvinceID] [int] NOT NULL,
  [PostalCode] [nvarchar](15) NOT NULL,
  PRIMARY KEY (AddressID))
GO

Step 3: Insertion of records to table Address. It shows two different ways of insertion of data.

Query:

INSERT into Address(AddressLine1,City,StateProvinceID,PostalCode)
values
('Address1,','Chennai',1,600028), -- 1st row of data
('Address2','Mumbai',2,400029), -- 2nd row of data
('Address3','Kolkata',3,700027), --3rd row
('Address4','Delhi',4,110999) -- 4th row
GO

--This will insert 1 row
INSERT into Address(AddressLine1,City,StateProvinceID,PostalCode)
values ('Address5,','Madurai',1,625010);
GO
--Next row insertion
INSERT into Address(AddressLine1,City,StateProvinceID,PostalCode)
values ('Address6','Pune',2,411062)
GO
--Next row insertion
INSERT into Address(AddressLine1,City,StateProvinceID,PostalCode)
values ('Address7','Hoogly',3,712501)
GO

SELECT  * from Address  -- Display the inserted records

Output:

Now the above query can be exported to a CSV file using azure studio easily in the below way:

• First select option save as CSV.

• This will easily provide the option to save the file as CSV and the contents are shown in the below image.

• We can see that the output got exported to CSV by means of a comma-separated manner.

Azure DataStudio makes the ways so easier. We have to fetch the data by means of Select query and easily it can be viewed as a CSV file.

Exporting data to CSV File Using SQL Server Management Studio

Here is a step-by-step process for exporting data to CSV file using SQL Server Management Studio

Step 1: Select database>>Tools>> options in SQL Server Management Studio.

Step 2: Next, under the Options, we can select the output format.

Output:

Hence exporting of data in CSV is done. By default, it will show the output in a grid pattern. So. both Azure data studio and SQL Server Management studio help in best to export data to CSV.