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.

Data_file

The full path of the data file used when bulk copy a table or view to disk (or copy from disk). When bulk copying data to SQL Server, this data file contains data that will be copied to the specified table or view. When you bulk copy data from SQL Server, the data file contains data that is copied from the table or view. The path can have 1 to 255 characters.

-M max_errors

Specifies the maximum number of errors that may occur before the bulk copy operation is canceled. Each row that bcp cannot copy will be ignored and counted as an error. If this option is not included, the default is 10.

-F Format_file

Specifies the full path of the format file that contains the storage responses that were previously used on the same table or view when using bcp. Use this option when bulk copying or copying of data using a format file created by the format option. The creation of a format file is optional. After several formatting questions are prompted, bcp prompts you to save the answer in the format file. The default file name is BCP.FMT. When bulk copying data, BCP can refer to a format file, so you do not have to re-enter the previous answer. If you do not use this option and you do not specify –n,-C,-W,-6, or-N, bcp prompts for the format information.

-E Err_file

Specifies the full path of the error file that is used to store all rows that bcp cannot transfer from the file to the database. Error messages from BCP are sent to the user station. If this option is not used, the error file is not created.

-F First_row

Specifies the ordinal number of the first row to bulk copy. The default value is 1, which indicates the first row in the specified data file.

-L Last_row

Specifies the ordinal of the last line to bulk copy. The default value is 0, which indicates the last row in the specified data file.

-B batch_size

Specifies the number of rows in each batch of data that is copied. Each batch is replicated to the server as a transaction. SQL Server commits or rolls back (on failure) each batch of transactions. By default, all data in the specified data file is replicated as a batch. Please do not use with the-h "rows_per_batch = bb" option.

-N

Perform bulk copy operations using the native (database) data type of the data. This option does not prompt for each field, and it will use native values.

-C

Performs bulk copy operations using character data types. This option does not prompt for each field; it uses char as the storage type, without a prefix, \ t (tab) as the field delimiter, \ n (newline character) as the line terminator.

-W

Performs bulk copy operations using Unicode characters. This option does not prompt for each field; it uses nchar as the storage type, without a prefix, \ t (tab) as the field delimiter, \ n (newline character) as the line terminator. cannot be used in SQL Server version 6.5 or earlier.

-N

Performs bulk copy operations on the native (database) data types that use data for non-character data and on character data using Unicode character types. This is a higher performance option than the-W option to transfer data from one SQL Server to another using a data file. It does not prompt for each field. You can use this option when you need to transfer data that contains ANSI extended characters and want to take advantage of native mode performance. You cannot use the-n option in SQL Server version 6.5 or earlier.

-V (60 | 65 | 70)

Perform bulk copy operations using data types from earlier versions of SQL Server. This option is used in conjunction with the character (-c) or native (-N) format. This option does not prompt for each field, and it uses the default value. For example, to bulk copy the date format supported by the bcp utility in SQL Server 6.5 (but ODBC no longer supports) to SQL Server 2000, you can use the-V 65 parameter.