在cmd中使用bcp 工具导出数据的问题

BCP是SQL Server中负责导入导出数据的一个命令行工具，它是基于DB-Library的，并且能以并行的方式高效地导入导出大批量的数据。BCP可以将数据库的表或视图直接导出，也能通过SELECT FROM语句对表或视图进行过滤后导出。在导入导出数据时，可以使用默认值或是使用一个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中。

下面将详细讨论如何利用BCP导入导出数据。

1. BCP的主要参数介绍

BCP共有四个动作可以选择。

(1) 导入。

这个动作使用in命令完成，后面跟需要导入的文件名。

(2) 导出。

这个动作使用out命令完成，后面跟需要导出的文件名。

(3) 使用SQL语句导出。

这个动作使用queryout命令完成，它跟out类似，只是数据源不是表或视图名，而是SQL语句。

(4) 导出格式文件。

这个动作使用format命令完成，后而跟格式文件名。

下面介绍一些常用的选项：

-f format_file

format_file表示格式文件名。这个选项依赖于上述的动作，如果使用的是in或out，format_file表示已经存在的格式文件，如果使用的是format则表示是要生成的格式文件。

-x

这个选项要和-f format_file配合使用，以便生成xml格式的格式文件。

-F first_row

指定从被导出表的哪一行导出，或从被导入文件的哪一行导入。

-L last_row

指定被导出表要导到哪一行结束，或从被导入文件导数据时，导到哪一行结束。

-c

使用char类型做为存储类型，没有前缀且以"t"做为字段分割符，以"n"做为行分割符。

-w

和-c类似，只是当使用Unicode字符集拷贝数据时使用，且以nchar做为存储类型。

-t field_term

指定字符分割符，默认是"t"。

-r row_term

指定行分割符，默认是"n"。

-S server_name[ instance_name]

指定要连接的SQL Server服务器的实例，如果未指定此选项，BCP连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例，只需要指定机器名即可。

-U login_id

指定连接SQL Sever的用户名。

-P password

指定连接SQL Server的用户名密码。

-T

指定BCP使用信任连接登录SQL Server。如果未指定-T，必须指定-U和-P。

-k

指定空列使用null值插入，而不是这列的默认值。

2. 如何使用BCP导出数据

(1) 使用BCP导出整个表或视图。

BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password" --使用密码连接

或

BCP AdventureWorks.sales.currency out c:currency1.txt -c -T --使用信任连接

下面是上述命令执行后的输出结果

Starting copy...

105 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)

下面是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

在使用密码登录时需要将-U后的用户名和-P后的密码加上双引号。

注：BCP除了可以在控制台执行外，还可以通过调用SQL Server的一个系统存储过程xp_cmdshell以SQL语句的方式运行BCP。如上述第一条命令可改写为

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password"'

执行xp_cmdshell后，返回信息以表的形式输出。为了可以方便地在SQL中执行BCP，下面的命令都使用xp_cmdshell执行BCP命令。

(2) 对要导出的表进行过滤。

BCP不仅可以接受表名或视图名做为参数，也可以接受SQL做为参数。通过SQL语句可以对要导出的表进行过滤，然后导出过滤后的记录。

EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -c -U"sa" -P"password"' (xp表示本存储过程是扩展的存储过程，extend　procedure，cmdshell是command　shell的意思，也就是直接敲命令执行。)

BCP还可以通过简单地设置选项对导出的行进行限制。

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"'

这条命令使用了两个参数-F 10和-L 13，表示从SELECT TOP 20 * FROM AdventureWorks.sales.currency所查出来的结果中取第10条到13条记录进行导出。

3. 如何使用BCP导出格式文件

BCP不仅可以根据表、视图导入导出数据，还可以配合格式文件对导入导出数据进行限制。格式文件以纯文本文件形式存在，分为一般格式和xml格式。用户可以手工编写格式文件，也可以通过BCP命令根据表、视图自动生成格式文件。

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format1.fmt -c -T'

上述命令将currency表的结构生成了一个格式文件currency_format1.fmt，下面是这个格式文件的内容。

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 "rn" 3 ModifiedDate

这个格式文件记录了这个表的字段（共3个字段）类型、长度、字符和行分割符和字段名等信息。

BCP还可以通过-x选项生成xml格式的格式文件。

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format2.fmt -x -c -T'

xml格式文件所描述的内容和普通格式文件所描述的内容完全一样，只是格式不同。

4. 如何使用BCP导入数据

BCP可以通过in命令将上面所导出的currency1.txt和currency2.txt再重新导入到数据库中，由于currency有主键，因此我们将复制一个和currency的结构完全一样的表。

SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency

将数据导入到currency1表中

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -T'

导入数据也同样可以使用-F和-L选项来选择导入数据的记录行。

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -F 10 -L 13 -T'

在导入数据时可以根据已经存在的格式文件将满足条件的记录导入到数据库中，不满足则不导入。如上述的格式文件中的第三个字段的字符长度是24，如果某个文本文件中的相应字段的长度超过24，则这条记录将不被导入到数据库中，其它满足条件的记录正常导入。

使用普通的格式文件

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -f c:currency_format1.fmt -T'

使用xml格式的格式文件

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -x -f c:currency_format2.fmt -T'

总结

BCP命令是SQL Server提供的一个快捷的数据导入导出工具。使用它不需要启动任何图形管理工具就能以高效的方式导入导出数据。当然，它也可以通过xp_cmdshell在SQL语句中执行，通过这种方式可以将其放到客户端程序中（如delphi、c#等）运行，这也是使客户端程序具有数据导入导出功能的方法之一。

xp_cmdshell

以 *** 作系统命令行解释器的方式执行给定的命令字符串，并以文本行方式返回任何输出。授予非管理用户执行 xp_cmdshell 的权限。

说明 在 Microsoft® Windows® 95 或 Microsoft Windows 98 *** 作系统中执行 xp_cmdshell 时，将不把 xp_cmdshell 的返回代码设置为唤醒调用的可执行文件的进程退出代码。返回代码始终为 0。

语法

xp_cmdshell {'command_string'} [, no_output]

参数

'command_string'

是在 *** 作系统命令行解释器上执行的命令字符串。command_string 的数据类型为 varchar(255) 或 nvarchar(4000)，没有默认值。command_string 不能包含一对以上的双引号。如果由 command_string 引用的文件路径或程序名称中有空格，则需要使用一对引号。如果使用嵌入空格不方便，可考虑使用 FAT 8.3 文件名作为解决办法。

no_output

是可选参数，表示执行给定的 command_string，但不向客户端返回任何输出。

返回代码值

0（成功）或 1（失败）

结果集

执行下列 xp_cmdshell 语句将返回当前目录的目录列表。

xp_cmdshell 'dir *.exe'

行以 nvarchar(255) 列的形式返回。

执行下列 xp_cmdshell 语句将返回随后的结果集：

xp_cmdshell 'dir *.exe', NO_OUTPUT

下面是结果：

The command(s) completed successfully.

注释

xp_cmdshell 以同步方式 *** 作。在命令行解释器命令执行完毕之前，不会返回控制。

当授予用户执行权限时，用户能在 Microsoft Windows NT® 命令行解释器上执行运行 Microsoft SQL Server™ 的帐户有权执行的任何 *** 作系统命令。

默认情况下，只有 sysadmin 固定服务器角色的成员才能执行此扩展存储过程。但是，也可以授予其他用户执行此存储过程的权限。

当作为 sysadmin 固定服务器角色成员的用户唤醒调用 xp_cmdshell 时，将在运行 SQL Server 服务的安全上下文中执行 xp_cmdshell。当用户不是 sysadmin 组的成员时，xp_cmdshell 将模拟使用 xp_sqlagent_proxy_account 指定的 SQL Server 代理程序的代理帐户。如果代理帐户不能用，则 xp_cmdshell 将失败。这只是针对于 Microsoft® Windows NT® 4.0 和 Windows 2000。在 Windows 9.x 上，没有模拟，且 xp_cmdshell 始终在启动 SQL Server 的 Windows 9.x 用户的安全上下文下执行。

说明 在早期版本中，获得 xp_cmdshell 执行权限的用户在 MSSQLServer 服务的用户帐户上下文中运行命令。可以通过配置选项配置 SQL Server，以便对 SQL Server 无 sa 访问权限的用户能够在 SQLExecutiveCmdExec Windows NT 帐户的上下文中运行 xp_cmdshell。在 SQL Server 7.0 中，该帐户称为 SQLAgentCmdExec。现在，不是 sysadmin 固定服务器角色成员的用户将在该帐户上下文中运行命令，而无需再进行配置更改。

权限

xp_deletemail 的执行权限默认授予 sysadmin 固定服务器角色的成员，但可以授予其他用户。

重要 如果为 MSSQLServer 服务选用的 Windows NT 帐户不是本地管理员组的成员，则非 sysadmin 固定服务器角色成员的用户将无法执行 xp_cmdshell。

示例

A. 返回可执行文件列表

下例显示执行目录命令的 xp_cmdshell 扩展存储过程。

EXEC master..xp_cmdshell 'dir *.exe'

B. 使用 Windows NT net 命令

下例显示 xp_cmdshell 在存储过程中的使用。下例先用 net send 通知用户 SQL Server 即将关闭，然后用 net pause 暂停服务器，最后用 net stop 关闭服务器。

CREATE PROC shutdown10

AS

EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down

in 10 minutes. No more connections allowed.', no_output

EXEC xp_cmdshell 'net pause sqlserver'

WAITFOR DELAY '00:05:00'

EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down

in 5 minutes.', no_output

WAITFOR DELAY '00:04:00'

EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down

in 1 minute. Log off now.', no_output

WAITFOR DELAY '00:01:00'

EXEC xp_cmdshell 'net stop sqlserver', no_output

C. 不返回输出

下例使用 xp_cmdshell 执行命令字符串，且不向客户端返回输出。

USE master

EXEC xp_cmdshell 'copy c:\sqldumps\pubs.dmp \\server2\backups\sqldumps',

NO_OUTPUT

D. 使用返回状态

在下例中，xp_cmdshell 扩展存储过程也给出了返回状态。返回代码值存储在变量 @result 中。

DECLARE @result int

EXEC @result = xp_cmdshell 'dir *.exe'

IF (@result = 0)

PRINT 'Success'

ELSE

PRINT 'Failure'

E. 将变量内容写入文件

下例将当前目录内容写入当前服务器目录下名为 dir_out.txt 的文件中。

DECLARE @cmd sysname, @var sysname

SET @var = 'dir /p'

SET @cmd = 'echo ' + @var + ' > dir_out.txt'

EXEC master..xp_cmdshell @cmd

例如：

1、写成存储过程，建立作业定时备份~~~

--在sql中映射一下就可以了

exec master..xp_cmdshell 'net use z: \\yizhi\D$ "密码" /user:yizhi\administrator'

--备份；with init覆盖|noinit添加

backup database 库名 to disk='E:\H_BACKUP.bak' with init

--COPY

exec master..xp_cmdshell 'copy E:\H_BACKUP.bak z:'

--删除（这句可以去掉）

--exec master..xp_cmdshell 'del E:\H_BACKUP.bak'

--完成后删除映射

exec master..xp_cmdshell 'net use z: /delete'

2、 *** 作DOC下的查询SQL语句，也就是osql的用法

用法: osql [-U 登录 ID] [-P 密码]

[-S 服务器] [-H 主机名] [-E 可信连接]

[-d 使用数据库名称] [-l 登录超时值] [-t 查询超时值]

[-h 标题] [-s 列分隔符] [-w 列宽]

[-a 数据包大小] [-e 回显输入] [-I 允许带引号的标识符]

[-L 列出服务器] [-c 命令结束] [-D ODBC DSN 名称]

[-q "命令行查询"] [-Q "命令行查询" 并退出]

[-n 删除编号方式] [-m 错误级别]

[-r 发送到 stderr 的消息] [-V 严重级别]

[-i 输入文件] [-o 输出文件]

[-p 打印统计信息] [-b 出错时中止批处理]

[-X[1] 禁用命令，[退出的同时显示警告]]

[-O 使用旧 ISQL 行为禁用下列项]

<EOF> 批处理

自动调整控制台宽度

宽消息

默认错误级别为 -1 和 1

[-? 显示语法摘要]

exec master..xp_cmdshell 'osql -U sa -P 19850709 -S (Local) -i E:\Study\SQL\Data\SqlText\cmdshellTest.sql'

注：路径的文件夹名称中间不能有空格

例如你安装xampp在本地D盘，（步骤都一样，注意位置）导入数据库步骤如下：

d:

cd

xampp\MySQL\bin\

xampp\mysql\bin\mysql

-u数据库名

-p密码

//

mysql>use

net2003

//找出要导入数据库

mysql>source

D:\数据库位置

即可

MySQL命令行导出数据库：

1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录如我输入的命令行：cd C:\Program Files\MySQL\MySQL Server 4.1\bin(或者直接将windows的环境变量path中添加该目录)2，导出数据库：mysqldump -u 用户名 -p 数据库名 >导出的文件名如我输入的命令行:mysqldump -u root -p news >news.sql (输入后会让你输入进入MySQL的密码)（如果导出单张表的话在数据库名后面输入表名即可）3、会看到文件news.sql自动生成到bin文件下

命令行导入数据库：

1，将要导入的.sql文件移至bin文件下，这样的路径比较方便2，同上面导出的第1步

3，进入MySQL：mysql -u 用户名 -p

如我输入的命令行:mysql -u root -p (输入同样后会让你输入MySQL的密码)4，在MySQL-Front中新建你要建的数据库，这时是空数据库，如新建一个名为news的目标数据库5，输入：mysql>use 目标数据库名

如我输入的命令行:mysql>use news

6，导入文件：mysql>source 导入的文件名

如我输入的命令行：mysql>source news.sql

MySQL备份和还原,都是利用mysqldump、mysql和source命令来完成的。

1.Win32下MySQL的备份与还原

1.1 备份

开始菜单 | 运行 | cmd |利用“cd \Program Files\MySQL\MySQL Server 5.0\bin”命令进入bin文件夹 | 利用“mysqldump -u 用户名 -p databasename >exportfilename”导出数据库到文件，如mysqldump -u root -p voice>voice.sql，然后输入密码即可开始导出。

1.2 还原

进入MySQL Command Line Client，输入密码，进入到“mysql>”，输入命令"show databases；"，回车，看看有些什么数据库；建立你要还原的数据库，输入"create database voice；"，回车；切换到刚建立的数据库，输入"use voice；"，回车；导入数据，输入"source voice.sql；"，回车，开始导入，再次出现"mysql>"并且没有提示错误即还原成功。

2.Linux下MySQL的备份与还原

2.1 备份

[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)[root@localhost mysql]# mysqldump -u root -p voice>voice.sql，输入密码即可。

2.2 还原

法一：

[root@localhost ~]# mysql -u root -p 回车，输入密码，进入MySQL的控制台"mysql>"，同1.2还原。

法二：

[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)[root@localhost mysql]# mysql -u root -p voice<voice.sql，输入密码即可。

---