• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1103
  • Last Modified:

Transact SQL to export dataset

Hello All,

Could some one tell me if/how it may be possible to take a transaction set, created from a basic SELECT statement and export it to a file as a TAB-delimited text file?

Thanks!
0
quiTech
Asked:
quiTech
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Anthony PerkinsCommented:
There are number of ways to do this with differing levels of effort/functionality:

1. SQL Query Analyzer.
2. DTS
3. BCP
4. OpenRowset
5. OSQL

Pick your poison.
0
 
quiTechAuthor Commented:
Ideally SQL Query Analyzer
0
 
Anthony PerkinsCommented:
In the Tools | Options | Results tab set:
1. "Default results target" to "Results to file ... "
2. "Result output format" to Tab Delimited.
3. Execute your query/store procedure.
4. Select an appropriate file name.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
quiTechAuthor Commented:
Ah... ok.  I was hoping for something I could put into a query, later to become a stored procedure.

Sorry, should have been more specific.
0
 
bruintjeCommented:
Hello quiTech,

you could use the sp_OAMethod to write to textfile from t-sql, a sample is included here
source: http://www.motobit.com/tips/detpg_SQLWrFile/

hope this helps a bit
bruintje
0
 
nmcdermaidCommented:
if you have admin rights you can run this:


xp_cmdshell 'BCP "SELECT Columns FROM YourDB.dbo.YourTable" queryout "C:\OutputFile.CSV" -c -T


Note that:

1. The table in the select needs to be a three part name, ie  DBame.dbo.ATable
2. C:\OutputFile.CSV is on the SQL server C drive.
0
 
nmcdermaidCommented:
Forgot closing quotes....


xp_cmdshell 'BCP "SELECT Columns FROM YourDB.dbo.YourTable" queryout "C:\OutputFile.CSV" -c -T'
0
 
quiTechAuthor Commented:
nmcdermaid,

I like that... seems pretty slick.

What is BCP?

0
 
nmcdermaidCommented:
BCP is a command line tool that comes with SQL Server. It can be used to import or export data.

If you go to the command prompt and type

BCP /?

it will list all the arguments.


xp_cmdshell is just a way of running something on the command line. (in this case BCP!)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now