Convert a table in SQL Server to a txt file

Method 1: Use the BCP command bcp utility in Microsoft? SQLServer? 2000 repeat data between an instance and a data file in the format specified by the user. Syntax bcp {[[database_name.] [owner].] {table_name | view_name} | query} {in | out | queryout | format} data_file [-mmax_err

Method 1: Use the BCP command bcp utility in Microsoft? SQL Server? 2000 repeat data between an instance and a data file in the format specified by the user. Syntax bcp {[[database_name.] [owner].] {table_name | view_name} | query} {in | out | queryout | format} data_file [-m max_err

Method 1: Use the BCP command

Bcp Utility

Bcp utility in Microsoft? SQL Server? 2000 repeat data between an instance and a data file in the format specified by the user.

Syntax

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

Name of the database where the specified table or view is located. If not specified, the default database is used.

Owner

Name of the table or view owner. If you have a specified table or view for the large-capacity copy operation, the owner is optional. If you do not specify the owner and do not have the specified table or view, Microsoft? SQL Server? 2000 the system will return an error message and cancel the large-capacity copy operation.

Table_name

The name of the target table when data is copied to SQL Server (in) and the name of the source table when data is copied from SQL Server (out.

View_name

Is the name of the target view for copying data to SQL Server (in) and the name of the source view for (out) Copying data from SQL Server. Only a view with all columns referencing the same table can be used as the target view. For more information about the restrictions on copying data to a view, see INSERT.

Query

Is a Transact-SQL query that returns a result set. If the query returns multiple result sets, for example, the SELECT statement of the specified COMPUTE clause, only the first result set will be copied to the data file, and the subsequent result set will be ignored. Use double quotation marks to cause a query statement, and use single quotation marks to cause any content embedded in the query statement. You must specify queryout when copying data from a large volume in the query.

In | out | queryout | format

Specifies the direction of large-capacity replication. In refers to copying from a file to a database table or view, and out refers to copying from a database table or view to a file. Queryout must be specified only when data is replicated from a large capacity in the query. Format creates a format file based on the specified options (-n,-c,-w,-6, or-N) and the table or view separator. If format is used, you must specify the-f option.

Note: The bcp utility in Microsoft SQL Server 6.5 does not support copying large data volumes to tables that contain SQL _variant or bigint data types.

Data_file

The complete path of the data file used to copy a large table or view to a disk (or from a disk. When you copy large data volumes to SQL Server, this data file contains data that will be copied to a specified table or view. When copying data from SQL Server in large capacity, the data file contains data copied from tables or views. The path can contain 1 to 255 characters.

-M max_errors

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

-F format_file

Specifies the full path of the format file. The format file contains the storage response when bcp is used in the same table or view. This option is used when you use a large-capacity copy of the format file created by the format option to copy in or out data. Format File Creation is optional. After several format questions are prompted, bcp will prompt whether to save and answer in the format file. The default file name is Bcp. fmt. When copying data in large capacity, bcp can reference a format file, so you do not have to re-enter the previous answer. If this option is not used and-n,-c,-w,-6, or-N is not specified, bcp will prompt you to enter the format information.

-E err_file

Specifies the full path of the error file, which is used to store all rows that bcp cannot transmit from the file to the database. The error message from bcp is sent to the user workstation. If this option is not used, no error file is created.

-F first_row

Specify the ordinal number of the first row to be replicated in large capacity. The default value is 1, indicating the first row of the specified data file.

-L last_row

Specify the ordinal number of the last row to be copied in large capacity. The default value is 0, indicating the last row in the specified data file.

-B batch_size

Specifies the number of rows in each batch of copied data. Each batch is copied to the server as a transaction. SQL Server submits or rolls back each batch of transactions (when a failure occurs. By default, all data in the specified data file is copied as a batch. Do not use it with the-h "ROWS_PER_BATCH = bb" option.

-N

Use the local (database) Data Type of the data to perform the large-capacity copy operation. This option does not prompt you to enter each field. It uses the local value.

-C

Use the character data type to perform a large-capacity copy operation. This option does not prompt you to enter each field. It uses char as the storage type, without a prefix, t (Tab) as the field separator, and n (line break) as the line terminator.

-W

Use Unicode characters to perform large-capacity copy operations. This option does not prompt you to enter each field. It uses nchar as the storage type, without a prefix, t (Tab) as the field separator, and n (line break) as the line terminator. It cannot be used in SQL Server 6.5 or earlier versions.

-N

The local (database) data type used for non-character data and the Unicode Character Type Used for character data are copied in large capacity. This is a higher performance option that can replace the-w option, with the aim of transferring data from one SQL Server to another using data files. It does not prompt you to enter each field. This option can be used to transmit data that contains ANSI extended characters and to take advantage of the performance of the local mode. You cannot use the-N option in SQL Server 6.5 or earlier versions.

-V (60 | 65 | 70)

Use the data types in earlier versions of SQL Server to perform large-capacity replication. This option is used with the character (-c) or local (-n) format. This option does not prompt you to enter each field. It uses the default value. For example, to copy large data formats supported by the bcp utility in SQL Server 6.5 (but not supported by ODBC) to SQL Server 2000, you can use the-V 65 parameter.

Important to copy data from SQL Server to a large data file, even if-V is specified, the bcp utility does not generate a date format of SQL Server 6.0 or SQL Server 6.5 for any datetime or smalldatetime data. The date is always written in ODBC format. In addition, SQL Server 6.5 or earlier versions do not support Empty bit Data. Therefore, the null value in the bit column is set to 0.

-6

Use the SQL Server 6.0 or SQL Server 6.5 data type to perform a large-capacity copy operation. To maintain backward compatibility. Use the-V option instead.

-Q