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

Checkmark for guaranteed SR = WR (2h)

How to connect to git in SQL Server 2016/2017 without using any third party tool (2h)

Sql restart (7h)

Excel column wise data save in rows (1d)

Date timzone conversion (1d)

Object cannot be cast from DBNULL to other types coming randomly in SSIS Package-Migrated from VS 2008 to 2015,SQL 2008R2 to SQL2016 on 1st run only (2d)

Error in sp procedure- Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0 (2d)

Two records into a single record? (2d)

- Advertisement -