Server数据导入导出工具BCP详解,使用脚本输出e
分类:美高梅-数据

转自:

SQL Server的导出导入方式有:

 SQL Server数据导入导出工具BCP详解


  1. 在SQL Server中提供了导入导出的界面操作。
  2. 在界面操作中又分【复制一个或多个表或视图的数据】和【编写查询以指定要传输的数据】两种模式,第一种是直接对表、视图进行全部字段、记录进行导出,而第二种就是可以通过SQL语句来控制导出导入的字段和行。
  3. 使用 简单但有用的SQL脚本 中的【表复制】这里面的方法。
  4. 再一种就是在命令行中使用bcp命令来导入导出数据,需要特别说明的是,这是对大数据量导入导出就好的办法。

    --整个表导出(out)
    bcp 数据库名.dbo.表名 out c:currency.txt -S"数据库实例" -U"用户" -P"密码" -c 

    --使用SQL语句导出(queryout)
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"用户" -P"密码" -c

    --设置字段分隔符和行分隔符(-c -t"," -r"n"),不想输入字段类型等请配合-c一起使用
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"用户" -P"密码" -c -t"," -r"n"

    --指定每批导入数据的行数、指定服务器发出或接收的每个网络数据包的字节数(-k -b5000 -a65535)
    bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"用户" -P"密码" -c -t"," -r"n" -k -b5000-a65535

    --在查询分析器上执行(EXEC master..xp_cmdshell)
    EXEC master..xp_cmdshell 'bcp "select * from 数据库名.dbo.表名" queryout c:currency.txt -S 数据库实例 -U"用户" -P"密码" -c'

    --把SQL语句生成一个.sql文件,然后调用
    --注:路径的文件夹名称中间不能有空格
    exec master..xp_cmdshell 'osql -S 数据库实例 -U 用户 -P 密码 -i    C:cmdshellTest.sql'  

    --将数据导入到currency表中
    EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:currency.txt -c -T'
    --导入数据也同样可以使用-F和-L选项来选择导入数据的记录行。
    EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:currency.txt -c -F 10 -L 13 -T'

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

美高梅4858官方网站 1

  1. bcp的主要参数介绍

摘要:

在使用命令xp_cmdshell的时候需要设置权限:

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

下文将分享使用sql脚本输出excel的方法
 
此脚本可以应用于 表或视图生成excel的方法,
若需使用sql脚本输出excel数据,我们可将sql脚本生成视图或临时表,

美高梅4858官方网站 2

(1) 导入。

然后再输出excel

输出excel语法简介:
exec
master..xp_cmdshell 'bcp [数据库名称].[架构名].[表名] out [excel存放位置全路径] -c -q -S"服务器Ip" -U "sql用户名" -P "sql密码"'

---例1:
exec 
master..xp_cmdshell 'bcp test.dbo.tableName out d:test.xls -c -q -S"." -U "sa" -P "erp"'
---将数据库test中tablName输出值d盘test.xls文件
---服务器地址.
---sql账户sa
---sql密码erp
--------------------------------------------
例2: 根据动态文件名输出excel的方法
declare @name varchar(30) ---动态文件名
set @name ='d:test123.xlsx'

exec 
('master..xp_cmdshell ''bcp test.dbo.tableName out '+@name+' -c -q -S"." -U "sa" -P "erp"''') ---拼接操作语句,并采用exec执行

注意事项:

*1 使用脚本输出的excel ,无表头
2 可动态文件名和动态sa账户密码生成相关信息
3 如果输出sql脚本的数据至excel,我们需先将sql脚本生成的数据缓存至表中,然后输出表数据至excel

xp_cmdshell相关权限需打开
*

/*MSsql2005 如何启用xp_cmdshell
默认情况下,sql server2005安装完后,xp_cmdshell是禁用的(可能是安全考虑),如果要使用它,可按以下步骤
*/
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO

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

--执行想要的xp_cmdshell语句
Exec xp_cmdshell 'query user'
GO

(2) 导出。

--用完后,要记得将xp_cmdshell禁用(出于安全考虑)
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--重新配置
RECONFIGURE
GO

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

美高梅4858官方网站 3

(3) 使用SQL语句导出。

美高梅4858官方网站 4

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

 

(4) 导出格式文件。

 

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

 

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

 

-f format_file

遇见的错误:

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

1、发生以下错误:
[Error][Microsoft][Native]Error = [Microsoft][SQL Native Client]无法打开 BCP 主数据文件

-x

使用如下命令:
EXEC xp_cmdshell 'ECHO %USERDOMAIN%%USERNAME%'
返回 :NT AUTHORITYNETWORK SERVICE

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

然后在配置管理器(configuration manager)里面的SQL server2005服务里打开,看到登陆内置账号为Network service,
改成local system问题解决。

-F first_row

2、SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]对于造型说明无效的字符值

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

如果是表与表之间的数据导入,可用 -N, 或者 -w, 而不要用 -c
用-c的话, 如果导出的某个列中的数据中包含分隔符, 则会导致你 bcp 导入的时候失败
-N 或者 -w 不会有这个问题

-L last_row

 

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

bcp用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
  [-m 最大错误数]             [-f 格式化文件]         [-e 错误文件]
  [-F 首行]                       [-L 末行]                  [-b 批大小]
  [-n 本机类型]                 [-c 字符类型]            [-w 宽字符类型]
  [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q 带引号的标识符]
  [-C 代码页说明符]           [-t 字段终止符]       [-r 行终止符]
  [-i 输入文件]                   [-o 输出文件]         [-a 数据包大小]
  [-S 服务器名称]              [-U 用户名]            [-P 密码]
  [-T 可信连接]                  [-v 版本]                [-R 允许使用区域设置]
  [-k 保留空值]                  [-E 保留标识值]
  [-h"加载提示"]                 [-x 生成 xml 格式化文件]

-c

美高梅网站是多少, 

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

 

-w

其它:

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

新建查询->输入SQL查询语句,执行得到需要的结果,在查询结果栏点击鼠标右键->将结果另存为 
美高梅4858官方网站,导出文件(*csv)即可导出为 逗号分割的excel文件。 
此时,由于没有分列,所以应先新建一个空白的Excel文档,然后选择打开刚才保存的的文件; 
然后它会出现文本导入步骤,选中分割符号,下一步,选中 逗号,预览区域正常,然后在下一步和完成,保存为excel文件

-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值插入,而不是这列的默认值。

  1. 如何使用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"'

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

这条命令使用了两个参数-F 10和-L 13,表示从SE

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

LECT TOP 20 * FROM AdventureWorks.sales.currency所查出来的结果中取第10条到13条记录进行导出。

  1. 如何使用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 "/r/n" 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格式文件所描述的内容和普通格式文件所描述的内容完全一样,只是格式不同。

  1. 如何使用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#等)运行,这也是使客户端程序具有数据导入导出功能的方法之一。

本文由美高梅网站是多少发布于美高梅-数据,转载请注明出处:Server数据导入导出工具BCP详解,使用脚本输出e

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文