Exporting records into ASCII format

By Bill Graziano on 13 August 2000 | 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.


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

Select a single row based on conditions in multiple rows (2h)

I want Help Managing Big Data Sets in T SQL Efficiently (13h)

SQL stored procedure to load the error and correct record based on some business rules (23h)

Query is running too long (1d)

Sql Query to check status change of an item (1d)

Can I create differential backups tied to a specifc Full backup instead of the most recent? (7d)

My informix Sql query retruns Null always (8d)

Vehicle availability query (9d)

- Advertisement -