Exporting records into ASCII format

By Bill Graziano on 13 August 2000 | 1 Comment | Tags: Import/Export


Sujatha writes "how to export all the records from a table in SQL server into an ASCII text file and from the ASCII text file import into the SQL Server table? How to do this in ASP or VB?" You can either use BCP or DTS for this . . .

BCP and Data Transformation Services (DTS) can both be used to export data from a table to a text file. BCP is the simpler of the two. It can be used to export from a single table or view to a text file. You really can't do much in the way of reformatting data inside BCP. It is very simple and very fast. When I use BCP I generally create a export table full or CHAR fields that exactly match the desired format of my ASCII text file. I write all the fancy SQL to manipulate my data into the export table and then use BCP to export the data. BCP is an operating system level command so you'll have to shell out to the operating system (see Books Online for XP_CMDSHELL). If you are using BCP to import data you can use the BULK INSERT statement.

DTS is more flexible, more complicated and harder to use (in my opinion). You create a graphical package for the export and call it using the operating system command DTSRUN. You can see Books Online for the detailed syntax of this command. DTS allows a much more complex extract. You can also write custom ActiveX transformations if you need to (and don't mind a SLOW extract).

I'd lean toward BCP myself.

Discuss this article: 1 Comment so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server (4 May 2007)

Using Fuzzy Lookup Transformations in SQL Server Integration Services (22 January 2007)

Import text files script (19 October 2003)

Uploading MDB to SQL Server Programmatically (9 August 2000)

Running Jobs (UNC vs Drive Letters ) (2 July 2000)

Other Recent Forum Posts

insert values of a column from one table to column (7 Replies)

SQL Server 2008 on windows 8 (1 Reply)

Calculate lengths (4 Replies)

Update values in two tables via stored procedure (4 Replies)

Update rows with increment date (19 Replies)

sqlit, transaction (1 Reply)

Return a row with null values (1 Reply)

Exporting SQL table to text from in vs2010 (2 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -