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.