How to turn a table in SQL Server into a TXT file

Method-: Using bcp command

bcp utility

The bcp utility microsoft®sql the data in a user-specified format between the server™2000 instance and the data file.
　
Grammar

BCP {[database_name.] [owner].] {table_name | view_name} | "Query"}
{in | out | queryout | format} data_file
[M max_errors] [F Format_file] [-E Err_file]
[F First_row] [-L Last_row] [B batch_size]
[-N] [-c] [W] [-N] [-V (60 | 65 | 70)] [-6]
[Q] [-C Code_page] [-T Field_term] [R Row_term]
[I. Input_file] [-O output_file] [-A packet_size]
[-S Server_name[\instance_name]] [-U login_id] [-p password]
[-T] [-V] [-R] [K] [-E] [-H "hint [,... n]"]

parameter database_name

the name of the database that contains the specified table or view. If not specified, the user default database.

owner

the name of the owner of the table or view. Owner is optional if the user performing the bulk copy operation has the specified table or view. If owner is not specified and the user performing the bulk copy operation does not have the specified table or view, Microsoft®sql server™2000 returns the error message and cancels the bulk copy operation.

table_name

is the destination table name for copying data to SQL Server (in), and the name of the source table (out) When copying data from SQL Server.

view_name

is the name of the destination view for copying data to SQL Server (in), and the name of the source view (out) When copying data from SQL Server. Only a view of all columns referencing the same table can be used as a destination view. For more information about the limitations of copying data to views, see INSERT.

Query

is a Transact-SQL query that returns a result set. If a query returns multiple result sets, such as a SELECT statement that specifies a COMPUTE clause, only the first result set is copied to the data file, and subsequent result sets are ignored. Use double quotes to cause a query statement to use single quotes to cause any content embedded in a query statement. When you bulk copy data from a query, you must also specify Queryout.

In | out | queryout | format

specifies the direction of bulk copy. In is copying from a file to a database table or view, out of which you copy from a database table or view to a file. You must specify queryout only when you bulk copy data from a query. Format creates a format file, depending on the option you specify (-N,-C,-W,-6, or-N), and the table or view separator. If you use format, you must also specify the-F option.

explains that the bcp utility in Microsoft SQL Server 6.5 does not support bulk copying to tables that contain sql_variant or bigint data types.

Total 3 page: previous 1 [2] [3] Next page