Shulou(Shulou.com)06/01 Report--

The BCP command has many parameters. Use-h to view the help information. Note: the parameters are case-sensitive.

Common parameters for exporting and importing data using the BCP command are as follows

Bcp {[[database_name.] [schema_name]]. {table_name | view_name} | "query"}

{in | out | queryout} data file

[- c character type] | [- w wide character type]

[- t field Terminator] [- r line Terminator]

[- I input file] [- o output file]

[- S server name] [- U user name] [- P password]

[- T trusted connection] [- d database name]

[- k keep null value]

-c uses the char type as the storage type, with no prefix and with "\ t" as the field separator and "\ n" as the line separator.

"- w copies the data using the Unicode character set, and in the database, you need to set Table Column to the nchar or nvarchar storage type." If-c and-w are specified at the same time, then-w overrides-c.

-t field_term specifies the column separator, which defaults to "\ t".

-r row_term specifies the row separator, which defaults to "\ n".

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

-U login_id specifies the user name to connect to the SQL Sever.

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

-T specifies that BCP logs in to SQL Server using a trusted connection. If-T is not specified,-U and-P must be specified.

-d specify the database name

-k specifies that empty columns are inserted with null values instead of the default values for this column.

First, use bcp to export the data from the entire table to a txt or csv document

Bcp db_study.dbo.sales out D:\ test.txt S. U sa P sa t S. U sa P sa t w

Second, use query statement to export query results to txt or csv documents

1. Configure SQL Server to allow xp_cmdshell commands to be run

-- allow configuration of advanced options EXEC master.sys.sp_configure 'show advanced options', 1-- reconfigure RECONFIGURE-- enable xp_cmdshell EXEC master.sys.sp_configure' xp_cmdshell', 1-- reconfigure RECONFIGURE

Otherwise, SQL Server will throw an error message

SQL Server blocked access to the process "sys.xp_cmdshell" of the component "xp_cmdshell" because the component has been shut down as part of the security configuration of this server. The system administrator can enable "xp_cmdshell" by using sp_configure. For more information about enabling xp_cmdshell, search for "xp_cmdshell" in SQL Server Books online.

Enabling "xp_cmdshell" is considered unsafe, and after using the "xp_cmdshell" command, disable it using the following script.

-- allow configuration of advanced options EXEC master.sys.sp_configure 'show advanced options', 1-- reconfigure RECONFIGURE-- disable xp_cmdshell EXEC master.sys.sp_configure' xp_cmdshell', 0Mel-- reconfigure RECONFIGURE

2. Use the xp_cmdshell command to run the BCP command to export the data

EXEC master..xp_cmdshell 'bcp "SELECT [Store], [Item], [Color], [Quantity] FROM [db_study]. [dbo]. [Inventory]" queryout D:\ test.txt-S. -U sa-P sa-t "\ t"-w'EXEC master..xp_cmdshell 'bcp "SELECT [Store], [Item], [Color], [Quantity] FROM [db_study]. [dbo]. [Inventory]" queryout D:\ test.csv-S. -U sa-P sa-t ","-w'

3. Using the xp_cmdshell command, you can also export the data of the entire Table.

EXEC master..xp_cmdshell 'bcp [db_study]. [dbo]. [Inventory] out D:\ test.txt-S.-U sa-P sa-t "\ t"-w'EXEC master..xp_cmdshell' bcp [db_study]. [dbo]. [Inventory] out D:\ test.csv-S.-U sa-P sa-t ","-w'

Third, import the data into SQL Server

CREATE TABLE [dbo]. [Inventory_LoadIn] ([Store] [nvarchar] (2) NULL, [Item] [varchar] (20) NULL, [Color] [varchar] (10) NULL, [Quantity] [int] NULL)

1. Import the data from the txt document into table [dbo]. [Inventory_LoadIn] using BCP

Bcp [db_study]. [dbo]. [inventory _ LoadIn] in D:\ test.txt-S. -U sa-P sa-t "\ t"-w bcp [db_study]. [DBO]. [inventory _ LoadIn] in D:\ test.csv-S. -U sa-P sa-t ","-w

2. Use the xp_cmdshell command to execute bcp and import the data into the database table

EXEC master..xp_cmdshell 'bcp [db_study] .[ inventory _ LoadIn] in D:\ test.txt-S.-U sa-P sa-t "\ t"-w'EXEC master..xp_cmdshell' bcp [db_study] .[ inventory _ LoadIn] in D:\ test.csv-S.-U sa-P sa-t ","-w'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.