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".
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.
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
