How to Output to a File in T-SQL

Written by rachelle reese
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Output to a File in T-SQL
A comma is often used as a delimiter between columns. (Hemera Technologies/AbleStock.com/Getty Images)

If you need to retrieve data from a SQL Server database and save it to a file, use the bulk copy program (bcp) Transact-SQL (T-SQL) utility. By default, the data is delimited with a tab between fields (columns) and a new line between rows. However, you can customise how the data is delimited by using bcp options. One benefit of using bcp is that you can automate the data retrieval by configuring a SQL Server job.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Computer running Microsoft SQL Server 2008
  • AdventureWorks database
  • Administrator account

Show MoreHide

Instructions

  1. 1

    Click the "Start" button. Click the "All Programs" menu item. Click the "Accessories" menu item. Right-click the "Command Prompt" menu item and choose "Run as administrator." If you are prompted to allow the program to make changes, click the "Yes" button.

  2. 2

    Type the following command and press the "Enter" key:

    bcp "SELECT Title, VacationHours, SickLeaveHours FROM HumanResources.Employee" queryout "C:\timeoff_tab.txt" -T -c

  3. 3

    Click the "Start" menu and choose the "Computer" menu item.

  4. 4

    Double-click the drive C icon.

  5. 5

    Double-click "timeoff_tab.txt" and verify that each row of the results is displayed on a separate line and each field is separated by a tab.

  6. 6

    Bring the Command Prompt window to the foreground.

  7. 7

    Type the following command and press the "Enter" key:

    bcp "SELECT Title, VacationHours, SickLeaveHours FROM HumanResources.Employee" queryout "C:\timeoff_comma.txt" -T -c -t ,

  8. 8

    Bring the Explorer window to the foreground.

  9. 9

    Double-click "timeoff_comma.txt" and verify that the fields are delimited by a comma.

Tips and warnings

  • To output a complete table or view, specify the table or view name and use the "out" option instead of the "queryout" option.
  • The -T option specifies that you are connecting using Windows authentication. If you were using SQL Server authentication, you would use the -U username and -P password options.
  • The -c option specifies that you are outputting character data.
  • The -t option specifies a field delimiter.
  • The -r option specifies a row delimiter.
  • The bcp options are case-sensitive.
  • If your AdventureWorks database is configured for a case-sensitive collation, you will need to reference the database, table and column names using the correct case.

Don't Miss

References

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.