Simple way to export SQL Server data to Text Files

最新推荐文章于 2024-06-24 21:46:19 发布

武哥博文

最新推荐文章于 2024-06-24 21:46:19 发布

阅读量1.4k

点赞数

分类专栏： 001-A 数据库开发文章标签： sql server ssis parameters character xp command

001-A 数据库开发专栏收录该内容

146 篇文章 0 订阅

订阅专栏

Simple way to export SQL Server data to Text Files

Written By: Ken Simmons -- 11/24/2008 -- 2 comments

Stay informed - get the MSSQLTips.com newsletter and win - click here

Problem
Since the transition from DTS to SSIS, I have found myself looking for alternative ways to do simple tasks such as exporting data from SQL Server into text files. SSIS is a far more powerful tool than DTS, but I generally try to stay away from it for simple operations. With the combination of a few T-SQL commands this tip shows you a simple way to export data to text files.

Solution
One of the ways I have worked around this is by using bcp with xp_cmdshell. It's fast, easy and I can integrate it right into my code.

The first thing you have to do is make sure xp_cmdshell is enabled. You can do this in one of two ways.

1. You can use sp_configure and execute the following script.

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

2. You can use the Surface Area Configuration Tool.

Select Surface Area Configuration for Features and check the Enable xp_cmdshell checkbox.

Now that you have xp_cmdshell enabled you are ready to export your files.

Here is a sample command that will export the results of the sysfiles table to a comma delimited file called bcptest.txt. Just copy and paste this into a query window and execute the query.

EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:/bcptest.txt" -T -c -t,'

Note: BCP is a command line utility and xp_cmdshell is only required to use it within a SQL Batch.

This is the output for the above command when run in the "master" database.

The parameters that were used are:

The queryout option allows you to specify a query to export. This could be as simple as the query we have are as complicated as you want. You can also create a view and select the data from a view.
The file name where the results will be stored is placed after the queryout option.
The -T parameter specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. You could use -P (password) and -U (user) if you want to use a SQL Server login.
The -c specifies the character dataype will be used for each field.
The -t parameter allows you to specify a field delimiter. The character after -t will be used to separate the data fields. If -t is removed, tab will be used as the default delimiter.

Another parameter you may need to use is -S to specify the server name. If you have a named instance, you will need to use this parameter. Here is an example connecting to server "DEVELOP" and instance "DEV1".

EXEC xp_cmdshell 'bcp "select name, type_desc, create_date from sys.objects" queryout "C:/bcptest2.txt" -T -SDEVELOP/DEV1 -c -t,'

Also be aware that the case of the parameter such as -t and-T makes a difference, so make sure you have correct case for the parameter you are using. You can find more options for BCP here: http://msdn.microsoft.com/en-us/library/ms162802.aspx

Next Steps