SQL server BCP export to excel

Command: exec master .. xp_mongoshell 'bcp "selct statement" queryout c: \ test.xls-w-S database server name-U "username"-P "password "'

Note:-S-U-P should be capitalized.
For example:
Exec master .. xp_mongoshell 'bcp "select top 10 * from rw_dbs_cz .. rmstpar" queryout c: \ rmstpar.xls-w-S magickerr-U "sa"-P "strong "'

BCP is a command line tool used in SQL Server to import and export data. It is based on DB-Library and can efficiently import and export large batches of data in parallel. BCP can be used to export tables or views of a database, or filter tables or views using the select from statement to export them. When importing and exporting data, you can use the default value or use a format file to import the data in the file to the database or export the data in the database to the file.

The following describes how to use BCP to import and export data.

1. Introduction to main BCP Parameters

BCP has four actions to choose from.
(1) import.
This operation is completed using the in command, followed by the file name to be imported.
(2) Export.
This operation is completed using the out command, followed by the file name to be exported.
(3) use SQL statements to export data.
This operation is completed using the queryout command. It is similar to out, but the data source is not the table or view name, but an SQL statement.
(4) export the format file.
This operation is completed using the format command, followed by the format file name.

The following describes some common options:

-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
This option must be used with-f format_file to generate an xml format file.

-F first_row
Specifies the row of the exported table or the row of the imported file.

-L last_row
Specifies the end of the exported table or the end of the imported data.

-C
Char is used as the storage type, with no prefix and "\ t" as the field delimiter, and "\ n" as the row delimiter.

-W
Similar to-c, it is used only when Unicode character sets are 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 line delimiter. The default value is "\ n ".

-S server_name [\ instance_name]
Specifies the instance of the SQL 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 user name used to connect to the SQL server.

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

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

-K
The null value is used to insert an empty column, rather than the default value of this column.

2. How to Use BCP to export data

(1) Use BCP to export the entire table or view.

BCP AdventureWorks. sales. currency out c: \ currency1.txt-c-U "sa"-P "password" -- connect with a password

Or

BCP AdventureWorks. sales. currency out c: \ currency1.txt-c-T -- use a trusted connection

The output result after the preceding command is executed is as follows:

Starting copy...
105 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total: 10 Average: (10500.00 rows per sec .)

The following is part of currency1.txt.

AED Emirati Dirham 1998-06-01 00:00:00. 000
AFA Afghani 1998-06-01 00:00:00. 000
.........
.........
ZWD Zimbabwe Dollar 1998-06-01 00:00:00. 000

When you log on with a password, you need to add double quotation marks to the user name after-U and the password after-P.

Note: In addition to running BCP on the console, you can also run BCP using SQL statements by calling a system stored procedure xp_cmdshell of SQL Server. For example, the first command can be rewritten

EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency out c: \ currency1.txt-c-U "sa"-P "password "'

After xp_cmdshell is executed, the returned information is output as a table. To facilitate the execution of BCP in SQL, the following commands use xp_cmdshell to execute the BCP command.

(2) filter the table to be exported.

BCP not only accepts table or view names as parameters, but also SQL as parameters. You can use SQL statements to filter the tables to be exported and then export the filtered records.

EXEC master.. xp_mongoshell 'bcp "select top 20 * FROM AdventureWorks. sales. currency" queryout c: \ currency2.txt-c-U "sa"-P "password "'

BCP can also restrict exported rows by simply setting options.

EXEC master .. xp_cmdshell 'bcp "select top 20 * FROM AdventureWorks. sales. currency "queryout c: \ currency2.txt-F 10-L 13-c-U" sa "-P" password "'

This command uses two parameters-F 10 and-L 13, which indicate that from select top 20 * FROM AdventureWorks. sales. in the result obtained by currency, 10th to 13 records are obtained for export.

3. How to Use BCP to export a format file

BCP not only allows you to import and export data based on tables and views, but also limits the import and export data with format files. A format file exists as a plain text file, which is in the general format and xml format. You can manually write a format file or use the BCP command to automatically generate a format file based on tables and views.

EXEC master .. xp_mongoshell 'bcp AdventureWorks. sales. currency format nul-f c: \ currency_format1.fmt-c-t'

The above command generates a format file currency_format1.fmt for the structure of the currency table. The content of this format file is as follows.

9.0
3
1 SQLCHAR 0 6 "\ t" 1 CurrencyCode SQL _Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\ t" 2 Name SQL _Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 "\ r \ n" 3 ModifiedDate

This format file records the type, length, character, row delimiter, and field name of the table field (three fields in total.

BCP can also generate xml format files using the-x option.

EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency format nul-f c: \ currency_format2.fmt-x-c-t'

The content described in an xml format file is exactly the same as that described in a common format file, but the format is different.

4. How to Use BCP to import data

Bcpcan use the InCommand to re-import the currency1.txtand currency2.txt imported by the role to the database. Because currency has a primary key, we will copy a table with the same structure as currency.

Select top 0 * INTO AdventureWorks. sales. currency1 FROM AdventureWorks. sales. currency

Import data to the currency1 table

EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-c-t'

You can also use the-F and-L options to select the record rows for data import.

EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-c-F 10-L 13-t'

When importing data, you can import qualified records to the database based on existing format files. For example, the character length of the Third Field in the preceding format file is 24. If the length of the corresponding field in a text file exceeds 24, this record will not be imported into the database, other matching records are imported normally.

Use common format files

EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-F 10-L 13-c-f c: \ currency_format1.fmt-t'

Use an xml Format File

EXEC master.. xp_mongoshell 'bcp AdventureWorks. sales. currency1 in c: \ currency1.txt-F 10-L 13-c-x-f c: \ currency_format2.fmt-t'

Summary

The BCP command is a quick data import and export tool provided by SQL Server. You can use it to import and export data in an efficient manner without starting any graphical management tool. Of course, it can also be executed in SQL statements through xp_cmdshell. In this way, it can be run in a client program (such as delphi and c, this is also one of the ways to enable the client program to import and export data.