SQL Server to text/CSV file export

There are several to export data from SQL server to text file. There are more than 8 way to export data from SQL server to a csv file.We will discuss here , three easy way to export data from SQL server to csv file.

Export query result to the external  csv file.
1)Open query analyzer, write your query.

2)From the menu, choose tool menu, select option.

3)A new window will be open. 
4)Select Query Result.

5)From the drop down select "Result to file ",then set location of your text file.

Now executive query. Now open your file, you will see that the result set is in you file.



Export table data
You can export the whole table data to csv. 

1)From the object Explorer, select database.Right click on database,a context menu will appear.Select Task>>Export Data.

 2)A new window will appear.Click on "Next".

3)Choose your authentication , then click on "Next".

 

4)Select Destination "Flat File Destination". Select "File Name" by Browsing your target file.then click on "Next"

5) Click on Next.

SQL Bulk copy
 
SQL server have a smart way to export data to the text file. SQL bulk copy is a good option to export data to external text file with very fast and accurate manner. Exporting data one by one row  is very time consuming a slow process. SQL bulk copy, copy data as a bulk  and write to file as a bulk. "bcp" is the comment used for bulk copy of data.


Below is the example of SQL bulk copy. We have created a table called "tbl_bulkcopy" and insert data into it.A text file is created called "bulkcopy.txt". Now using "bcp" command to export the data to the text file.

--1. we are createing a table first

CREATE table tbl_bulkcopy

(

id    INT identity,

name  VARCHAR(50),

roll  INT

)

--insert data to the table

INSERT INTO tbl_bulkcopy(name,roll) VALUES('Ayan',20)

INSERT INTO tbl_bulkcopy(name,roll) VALUES('John',21)

--declare some use ful variable

DECLARE @fp             NVARCHAR(200)

DECLARE @fn             NVARCHAR(200)

DECLARE @sql            NVARCHAR(200)

DECLARE @opqry            NVARCHAR(200)

--set values of the variable

SELECT @fp ='C\copypath\'

SELECT @fn ='bulkcopy.txt'

SELECT @opqry='type '+@fp+@fn

---

---write the select query

SELECT @sql='SELECT name ,roll FROM tbl_bulkcopy'

---create the bulk copy script

SET @sql =  'bcp ' + '"' +  @sql + '"' +  '  queryout "' + @fp + @fn +'"   -c -q -C1252 -T '

--run the script in command shell

EXECUTE master..xp_cmdshell @sql

--

--to see the output of the file , please write the code

--or you can open the file directly

EXECUTE master..xp_cmdshell @opqry