FAQ: export query result set into file


	About Us
	SSWUG Articles
	SSWUG Articles
	FAQ

	Administration
	Backup/Restore
	Connectivity
	Development
	General
	Installation
	OLAP
	Replication
	Transfer/move
	Trouble
	SQL 6.5

	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

How can I export a query result set into file?

Answer:

There are several ways to do it.

1. You can use bcp utility.
Read about bcp utility in SQL Server Books online. This is the example to export data from the authors table in the pubs database into authors.txt file on the drive C:

EXEC master..xp_cmdshell
'bcp "select * from pubs..authors" queryout c:\authors.txt -c -Usa -P'

2. You can use osql or isql utility.
This is the example to export data from the authors table in the pubs database into authors.txt file on the drive C:

EXEC master..xp_cmdshell
'osql -S ServerName -U sa -P -q "select * from pubs..authors" -o c:\authors.txt'

3. You can use DTS Export Wizard.

4. You can use SQL-DMO.
(sp_OACreate, sp_OAMethod, sp_OAGetProperty, sp_OASetProperty and so on).

See this article for more details:
Working with COM objects from within T-SQL

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc