How can I create a plain-text flat file from SQL Server as input to another ap

How can I create a plain-text flat file from SQL Server as
input to another application?

Question Posted / guest

2 Answers
10427 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How can I create a plain-text flat file from SQL Server as input to another application?..

Answer / guest

A. One of the purposes of Extensible Markup Language (XML)
is to solve challenges like this, but until all
applications become XML-enabled, consider using our faithful
standby, the bulk copy program (bcp) utility.
This utility can do more than just dump a table; bcp also
can take its input from a view instead of from a table.
After you specify a view as the input source, you can limit
the output to a subset of columns or to a subset of rows by
selecting appropriate filtering (WHERE and HAVING) clauses.

More important, by using a view, you can export data from
multiple joined tables. The only thing you cannot do is
specify the sequence in which the rows are written to the
flat file, because a view does not let you include an ORDER
BY clause in it unless you also use the TOP keyword.

If you want to generate the data in a particular sequence or
if you cannot predict the content of the data you want to
export, be aware that in addition to a view, bcp also
supports using an actual query. The only "gotcha" about
using a query instead of a table or view is that you must
specify queryout in place of out in the bcp command line.

For example, you can use bcp to generate from the pubs
database a list of authors who reside in California by
writing the following code:

bcp "SELECT * FROM pubs..authors WHERE state = 'CA'"
queryout c:\CAauthors.txt -c -T -S

Is This Answer Correct ?

2 Yes

3 No

How can I create a plain-text flat file from SQL Server as input to another application?..

Answer / skybeaver

Use the BCP utility

bcp out Database.dbo.TableName FileName.txt -T -c

Is This Answer Correct ?

0 Yes

2 No

Post New Answer