SQL Server Database Import and Export tool BCP

You can use the BCP tool to export objects in the database as data files. The specific operations are as follows:

1. Contains query statements

Exec master .. xp_mongoshell 'bcp "select * from database. DBO. tablename" queryout "C: \ a.txt "-S"Databaseserver"-c-T'

 

2. query statements not included

Exec master .. xp_mongoshell 'bcp "database. DBO. tablename" out "C: \ a.txt "-S"Databaseserver"-c-T'

---------------------------------

Several common parameters:-F format_file

Format_file indicates the format file name. This option depends on the preceding action. If in or out is used, format_file indicates an existing format file. If format is used, it indicates a format file to be generated.

-X must be used with-F format_file to generate an XML format file.

-F first_row specifies which row of the exported table to export or which row of the imported file to import.

-L last_row specifies the row to which the exported table is to be imported or the end of data import from the imported file.

-C uses the char type as the storage type, with no prefix and "\ t" as the field delimiter, and "\ n" as the row delimiter.

-W is similar to-C. It is used only when the Unicode character set is used to copy data, and nchar is used as the storage type.

-T field_term specifies the delimiter. The default Delimiter is "\ t ".

-R row_term specifies the row delimiter. The default value is "\ n ".

-S SERVER_NAME [\ instance_name] specifies the instance of the SQL Server server to be connected. If this option is not specified, BCP connects to the default instance of the local SQL Server. To connect to the default instance on a machine, you only need to specify the machine name.

-U login_id specifies the username used to connect to the SQL server.

-P password specifies the username and password used to connect to SQL Server.

-T indicates that BCP uses a trusted connection to log on to SQL Server. -U and-P must be specified if-T is not specified.

-K specifies that the empty column is inserted with a null value, rather than the default value of this column.
-Q: If the system prompts set quto_identifier ON/OFF, add this parameter.

Permission:
The BCP out operation requires the select permission on the source table.
The BCP in operation requires at least select/Insert permission on the target table.
When the imported table has check constraints and triggers, the default action is to close. do not specify the-H option, check_constraints, and fire_triggers prompts. Therefore, you must have the alter table permission on the table.

 
Note: All parameters must be added with "" case sensitive