Extract data and export data using bcp + SQL statements

Recently, due to project requirements, data in the project database needs to be extracted according to certain rules and then imported to another database with the same structure. Previously, the bcp function was used, however, only bcp is used to import the data of the entire table to a csv file. After checking the parameters of the bcp command, it is found that the bcp command can be followed by an SQL statement, followed by the-queryout parameter, you can extract the data according to your own logic and import it to the csv file. However, you still encounter many difficulties in actual use, finally, we were able to complete the process and accumulated the following small experiences.
1. the bcp Command can be run directly in the Command window. You can click Start Menu-run-cmd-to enter the Command window, and then enter the compiled bcp statement
2. You can call the system stored procedure master .. xp_mongoshell to call the bcp command.
3. If you need to use the stored procedure of master. xp_cmdshell, you must first enable the xp_cmdshell function of sqlserver. You can go to sqlserver peripheral Configuration Manager-function Configuration Manager-select xp_cmdshell-to enable it. You can also enable the xp_cmdshell function by executing an SQL statement as follows:
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp _ cmdshell', 1 -- 1, enable; 0, disable
RECONFIGURE WITH OVERRIDE

4. When calling the master .. xp_mongoshell to call bcp, the entire bcp command should be placed in single quotes as a parameter of the master .. xp_mongoshell stored procedure, such:
Exec master .. xp_mongoshell 'bcp SQL statement-queryout d: \ test.csv-Usa-Ppassword-S127.0.0.1-C'
5. The name of the table involved in the SQL statement must be in the format of database. Owner. Table name, such as SchoolDataBase. dbo. School.
6. SQL statements need to be placed in double quotation marks. in actual application, there are too many single quotation marks and double quotation marks, and the nesting still needs to be changed. The string connection method is used, for example:
"Exec master .. xp_mongoshell 'bcp \" "+ SQL statement +" \ "-queryout d: \ test.csv-Usa-Ppassword-S127.0.0.1-C '"
7. the SQL statement cannot have a carriage return or line break, which I discovered only after half a day. No way, I can only save the country by curve, so the following SQL statement appears:
"Exec master .. xp_cmdshell 'bcp \ "" + SQL statement. replace ("\ r \ n", "") + "\"-queryout-Usa-Ppassword-S127.0.0.1-C '"
 

In this way, place the preceding statement as an SQL statement in sqlCommand for execution.

After some hard work, we can finally extract data and export it.